1

Тема: VBA: INSERT в xls через ADO

Доброго дня.

Имеется xls файл с одним листом, 10 колонок - создан через ADOX. Необходимо добавлять в него данные.

Из vbs всё отлично работает(в том числе и вариант с получением ссылки на ActiveConnection из ADOX после создания файла). Вот "скелет":


Option Explicit
' константы для работы с ADO
Const adUseClient = 3 : Const adSchemaTables = 20 : Const adSchemaColumns = 4
' Типы данных ADOX, доступные для Excel:
Const adDouble = 5, adDate = 7, adCurrency = 6, adBoolean = 11, adVarWChar = 202, adLongVarWChar = 203

Dim oConn
Set oConn = CreateObject("ADODB.Connection")
With oConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Extended Properties").Value = "Excel 8.0;"
  .CursorLocation = adUseClient
  .Open "C:\Temp\222.xls"
End With
Dim i
For i = 1 To 10
  oConn.Execute "INSERT INTO Report  VALUES ('PLEE022022', '13.01.2011', 'LLP LOGISTS', 'NORD WACE NUFF', '1003776', '13.01.2011', '12204.35', '1042.15', '780', '1008')"
Next
Set oConn = Nothing

Но vbs это так, для тестов и отладок. А работать это должно из ADP-шного проекта(MS Access 2003). Вот код процедуры:


Sub main()
    Dim oConn
    Set oConn = CreateObject("ADODB.Connection")
    With oConn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Extended Properties").value = "Excel 8.0;"
      .CursorLocation = adUseClient
      .Open "C:\Temp\222.xls"
    End With
    Dim i
    For i = 1 To 10
      oConn.Execute "INSERT INTO Report  VALUES ('PLEE022022', '13.01.2011', 'LLP LOGISTS', 'NORD WACE NUFF', '1003776', '13.01.2011', '12204.35', '1042.15', '780', '1008')"
    Next
    Set oConn = Nothing
End Sub

При попытке выполнить запрос(oConn.Execute) получаю ошибку: "В операции должен использоваться обновляемый запрос."(если кому-то поможет с диагонозом, то на момент ошибки св-во соединения SQLState = 3073).

Перекопал много, копал инет и на русском и на буржуйском, и на ms и на сторонних - натыкался только на вариант с отсутствующими правами доступа на файл(в основном касается asp-шников). Но файл вполне доступен(даже права полные для "Все" поставил - никак не вставляется).
Варианты с некорректными запросами, уникальностью данных и связанными таблицами тоже не подходят - таблица одна, ни с кем не связана. Все колонки создавались текстовыми. Про уникальность никаких намёков...

Сравнивал ConnectionString в vbs и в vba - одно и тоже, до последнего символа. Пробовал имя таблицы(листа) и в квадратные скобки заключать, и с долларом писать - не выходит каменный цветок .

Может кто-то с таким сталкивался и знает как лечить?

P.S. вариант работы с xls через Excel не подходит, т.к. офис на компьютере где необходим этот функционал отсутствует.

2

Re: VBA: INSERT в xls через ADO

BeS Yara, приведите ещё сам код для создания файла.

3

Re: VBA: INSERT в xls через ADO

alexii пишет:

BeS Yara, приведите ещё сам код для создания файла.

Из проекта приводить не буду(много править), приведу standalone вариант под vbs(под vba практически тоже самое):


' VB Script Document
option explicit
'====================================================
' http://support.microsoft.com/kb/303814/en-us#7
'ADO recognizes six data types in an Excel datasource that you can use to create columns:
'adDouble, type 5
'adDate, type 7
'adCurrency, type 6
'adBoolean, type 11
'adVarWChar, type 202
'adLongVarWChar ("memo"), type 203
'====================================================
Const adDouble = 5, adDate = 7, adCurrency = 6, adBoolean = 11, adVarWChar = 202, adLongVarWChar = 203

' http://support.microsoft.com/kb/303814
' http://support.microsoft.com/kb/303814/en-us
' You can, however, create a new Excel workbook by specifying a new workbook filename for your Catalog and appending at least
' one Table to the new Catalog, using the techniques discussed below. This method will create a new workbook with only the
' worksheets you have created as ADOX tables; in other words, the default number of blank sheets specified for a new Excel
' workbook through the Excel Options dialog box will not be added.
Dim cat
Dim tbl
Dim col
Set cat = Createobject("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\Temp\book7.xls;Extended Properties=Excel 8.0"
Set tbl = Createobject("ADOX.Table")
tbl.Name = "TestTable"
Set col = Createobject("ADOX.Column")
With col
    .Name = "Col1"
    .Type = adVarWChar
End With
tbl.Columns.Append col
Set col = Nothing
Set col = Createobject("ADOX.Column")
With col
    .Name = "Col2"
    .Type = adVarWChar 
End With
tbl.Columns.Append col
cat.Tables.Append tbl

Dim i, oRs, oCon, oCmd
Set oCon = cat.ActiveConnection

For i = 1 To 10
  'wscript.echo "INSERT INTO TestTable VALUES ('"& i & "', '" & i*2 & "')"
  Set oRs = oCon.Execute("INSERT INTO TestTable VALUES ('"& i & "', '" & i*2 & "')")
Next

Set oRs = Nothing
Set oCon = Nothing
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing

P.S. погода жесть - ехал по МКАД, разметки вообще не видно...

4 (изменено: BeS Yara, 2012-02-17 15:45:11)

Re: VBA: INSERT в xls через ADO

Вопрос снимается - дело было не в бобине...

В Microsoft Office Access 2003 или Microsoft Access 2002 запрещается вносить изменения, добавлять или удалять данные, источником которых являются книги Excel.
<...>
Из-за возникающих юридических вопросов корпорация Майкрософт отключила функции в Access 2003 и в Access 2002, которые позволяли пользователям изменять данные в связанных таблицах, которые указывали на диапазон в книге Excel.

Другими словами, через Application.Excel пожалуйста, через ADO/DAO - только почитать.

Свою задачу решил через создание временной таблицы(мне вариант показался самым простым) с последующим экспортом её через имеющийся в Access механизм(знал бы о нём раньше - не узнал бы об ADOX, который может потом пригодиться). Примерно так:


Sub MkRep01()
    Dim strSQL As String
    strSQL = "IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb..##TMPTable')) DROP TABLE ##TMPTable " & _
            "CREATE TABLE ##TMPTable (" & _
            "[Order] varchar(10), " & _
            "[Year] smallint, " & _
            "[Month] smallint, " & _
            "inv_date datetime, " & _
            "Credit_Note bit, " & _
            "currency varchar(3), " & _
            "Amount money, " & _
            "Amount0 money, " & _
            "Amount1 money, " & _
            "OShort_Name nvarchar(50))"
    Dim cnn As New ADODB.Connection
        cnn.Open get_connection_string()
    Dim com As New ADODB.Command
        com.ActiveConnection = cnn
        com.CommandText = strSQL
    'создаём временную таблицу
    cnn.Execute (strSQL)
    strSQL = "INSERT INTO ##TMPTable " & _
            "SELECT TOP 100 PERCENT " & _
            "Prefix + RIGHT('000000' + CAST(Number AS VARCHAR), 6) AS [Order], " & _
            "[Year], " & _
            "[Month], " & _
            "Invoice_Date AS inv_date, " & _
            "Credit_Note, " & _
            "Currency_ID AS currency, " & _
            "Amount, " & _
            "Amount0, " & _
            "Amount1, " & _
            "OShort_Name " & _
            "FROM dbo.MR_Order_QRY WHERE Invoice_Date BETWEEN '" & Format(Me.DTPickerFrom.value, "yyyy-mm-dd") & "' AND '" & _
                                    Format(Me.DTPickerTo.value, "yyyy-mm-dd") & "' ORDER BY Invoice_Date"
    'заполняем временную таблицу
    cnn.Execute (strSQL)
    'экспортируем данные из временной таблицы  в эксель
    If Me.chkOpenInExcel.value = 0 Then
            DoCmd.OutputTo acOutputTable, "##TMPTable", acSpreadsheetTypeExcel8, Me.txtFilePath.value, False
        Else
            DoCmd.OutputTo acOutputTable, "##TMPTable", acSpreadsheetTypeExcel8, Me.txtFilePath.value, True
    End If
End Sub

Проверил, работает и без установленного офиса, что мне и требовалось.

5

Re: VBA: INSERT в xls через ADO

Оригинальный подход у ребят, ничего не скажешь…

Но главное — Вы-таки откопали причину. Я, вслед за Вами, лопатил Ваш код и так же не нашёл причину неработоспособности его из-под Office.

6

Re: VBA: INSERT в xls через ADO

Да, помучился .
С другой стороны наткнулся на ADOX - в ряде случаев может оказаться очень полезным, для вывода данных из БД, например.

Если кому-то пригодится, вот мини-реферат который я для себя сделал(пример создания и заполнения таблицы есть в третьем посте):
______________________________________________________________________

"Microsoft ADO Ext. 2.8 for DDL and Security" aka ADOX.

Является надстройкой над ADO. Помимо прочих интересных возможностей, даёт возможность создать файл xls в отсутствии установленного MS Office.

При этом имеются ограничения:

- .Create для XLS не работает(выдаёт ошибку), т.к. не бывает пустых рабочих книг. Новую рабочую книгу можно создать только вместе с листом(пример для VBA - "How To Use ADOX with Excel Data from Visual Basic or VBA" [Creating Columns When Creating a Table]). Пустой MDB создаёт без проблем.
- Число типов данных ограничено(для Excel), хотя и достаточно(kb303814:ADO Data Types Used with Excel):
           adDouble, type 5
           adDate, type 7
           adCurrency, type 6
           adBoolean, type 11
           adVarWChar, type 202 [строка в Юникоде длиной в 255 символов (DT_WSTR)]
           adLongVarWChar ("memo"), type 203 [текстовый поток в Юникоде (DT_NTEXT)]
- Текстовые поля в колонки созданные через ADOX начинаются с апострофа('). На работу с полем из VBA похоже не влияет.
- Формулу добавленная в ячейку будет всего лишь строкой. Превратить текст в фомулу минуя ExcelApplication видимо не удастся. При этом для "родного" файла Excel достаточно переввести формулу средствами Excel, а для файла созданного через ADOX придётся ещё и апостроф убирать(из макроса достаточно переприсвоить значение, т.к. апостраф там не виден: Selection.Value = Selection.Value).

Есть несколько способов обратиться к таблицам в файле XLS (Пример использования технологии ADO для чтения и записи данных в книге Excel):
-Имя листа, а затем знак доллара (например, [Лист1$] или [Мой лист$]). Книги таблицы, на который ссылается таким образом состоит из всего используемого диапазона листа.
           oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
- Используйте диапазон с определенным именем (например, ["Table1"]).
           oRS.Open "Select * from Table1", oConn, adOpenStatic
- Использовать диапазон с конкретного адреса (например, [Лист1$ A1: B10]).
           oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic

Итого: формулы, форматирование, сложные отчёт - для этого потребуется один из офисов, для вывода данных(с названием колонок или без) в формат XLS на компьютере где отсутствует Microsoft Office вполне можно воспользоваться ADOX. Полученный файл корректно открывается и в OOo.

______________________________________________________________________
Имеются и другие возможности(например по части задания прав доступа), которые в данный момент мне не актуальны, потому не упомянуты особо.
P.S. А тэга для оформления списков в движке форума нет, или я плохи искал?

7

Re: VBA: INSERT в xls через ADO

P.S. А тэга для оформления списков в движке форума нет, или я плохи искал?

На этом движке вроде как нет.

Подытожьте сделанные Вами выводы в Коллекцию, пожалуйста.

И — добро пожаловать!

8

Re: VBA: INSERT в xls через ADO

alexii пишет:

Подытожьте сделанные Вами выводы в Коллекцию, пожалуйста.

И — добро пожаловать!

Благодарю. Постараюсь подытожить самое полезное(с моей точки зрения) не забыв о прочих возможностях .

9

Re: VBA: INSERT в xls через ADO

Спасибо!

10 (изменено: max7, 2012-09-20 23:35:10)

Re: VBA: INSERT в xls через ADO

Эх, тоже в своё время попался на этом.
Пришлось установить excel'чик и msdasql.

Доступ к файлам Excel 97-2003 при установленном excel'е возможен так:

var myFileFullPath = ...;
...
var objConnection = new ActiveXObject("ADODB.Connection");
objConnection.ConnectionString = 
   "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DefaultDir=" + 
   myFileFullPath.substring(0, myFileFullPath.lastIndexOf("\\")) + 
   ";DBQ=" +
   myFileFullPath +
   ";ReadOnly=0;FirstRowHasNames=1;";
...
objConnection.Open();

При этом, если открыть файл в excel'е, процесс изменения данных в файле можно параллельно/одновременно наблюдать в окне excel'я.
Иногда очень эффектно получается

11

Re: VBA: INSERT в xls через ADO

а подскажите как получить таблицу из MS SQL и вставить в эксель без ОДБЦ?

12

Re: VBA: INSERT в xls через ADO

А зачем без?

13

Re: VBA: INSERT в xls через ADO

а для ОДБЦ нужно создавать источник
с ним у меня получается, но у нас в организации over 300 компов - не хочется по всем ходит и прописывать

если есть возможность создать его программно буду благодарен

14

Re: VBA: INSERT в xls через ADO

а для ОДБЦ нужно создавать источник

Ах вон Вы про что… Строка соединения нужна для чего: для SQL Server или для Excel?

15

Re: VBA: INSERT в xls через ADO

у меня в Экселе 2 строки и кнопка (минимальный отчет)
по нажатию оно выполняет запрос и выводит на второй лист результат

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=cs001;Description=cs001;UID=read;password=read;APP=Microsoft Office 2003;WSID=s2;DATABASE=ankDic;" _
), Array("on=Yes")), Destination:=Worksheets("Данные").Range("A1"))
.CommandText = sqlstring
.Name = "??????"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With

Вот хотелось что бы на любом компьютере работало без создания строки подключения

16

Re: VBA: INSERT в xls через ADO

Без строки подключения работать не будет — иначе откуда он возьмёт информацию, куда и как подключаться.

Вы, наверное, хотели сказать — чтобы работало без необходимости создания источника DSN?

17

Re: VBA: INSERT в xls через ADO

alexii пишет:

..чтобы работало без необходимости создания источника DSN?

абсолютно верно

18

Re: VBA: INSERT в xls через ADO

пользуйте оле, оно при наличие офиса на порядок быстрее - оспаривайте.

Я конечно далек от мысли... (с)

19 (изменено: qwertEHOK, 2012-12-12 11:04:26)

Re: VBA: INSERT в xls через ADO

а можно примерчик?

готов хоть веревками вить, лишь бы работало

20 (изменено: BeS Yara, 2012-12-13 10:29:00)

Re: VBA: INSERT в xls через ADO

qwertEHOK пишет:

а можно примерчик?

готов хоть веревками вить, лишь бы работало

Из Excel можно связываться с SQL-сервером через ADODB. Создаём подключение, подключаемся, выполняем запрос - получаем recordset. По рекордсету проходим, и на основании этих данных заполняем отчёт в экселе.

Примеры можно поискать по форуму("ADODB.Connection"), можно посмотреть в MSDN(ADO Code Examples VBScript). Строка подключения будет прописана непосредственно в экселевском файле, и не будет нужды ходить по 300 рабочим местам. Пример строк подключения к различным источникам можно посмотреть на www.connectionstrings.com(или в MSDN ).

Под рукой примеров работы в vba из SQL в Excel нет, обычно в обратном порядке и на vbscript. Соорудил из подручных материалов пример на vbscript(надеюсь не слишком сырой):

Dim oConn, oRecordset
Set oConn = CreateObject("ADODB.Connection")
ConnectString = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & dbName & ";UID=" & user & ";PWD=" & pass
oConn.ConnectionString = ConnectString
oConn.ConnectionTimeOut = 15
oConn.CommandTimeout = 30
oConn.Open
Dim strSQL
'1. запрашиваем track_id из track по TIR
strSQL = "SELECT TOP 100 PERCENT track_id FROM track WHERE TIR LIKE '%" & TIR & "%'"
On Error Resume Next ' не останавливаемся на ошибках
Set oRecordset = oConn.Execute(strSQL)
If oConn.Errors.Count > 0  Then
      Dim E
      For Each E In oConn.Errors
        wscript.echo "При выполнении запроса возникла ошибка: " & E.Description
      Next
End If
If oRecordset.EOF Then
    wscript.echo "нифига нету"
  Else
    wscript.echo "получены данные"
    Do Until oRecordset.EOF
        'работаем с записью, обрабатываем возможные ошибки
        oRecordset.MoveNext
    Loop
End If
On Error Goto 0
oConn.Close
Set oConn = Nothing
Set oRecordset = Nothing

Попозже, если время будет, сделаю для vba.
P.S. два косяка уже нашел(копипаста ). Лучше подождать vba варианта.

21 (изменено: BeS Yara, 2012-12-13 11:34:14)

Re: VBA: INSERT в xls через ADO

Пример на vba:

Option Explicit

Sub main()
    Dim ServerName, dbName, user, pass, ConnectString
    ServerName = "localhost"
    dbName = "local_track"
    user = "user"
    pass = "password"
    
    Dim oConn, oRecordset
    Set oConn = CreateObject("ADODB.Connection")
    ConnectString = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & dbName & ";UID=" & user & ";PWD=" & pass
    oConn.ConnectionString = ConnectString
    oConn.ConnectionTimeOut = 15
    oConn.CommandTimeout = 30
    oConn.Open
    Dim strSQL
    ' запрашиваем данные из БД
    strSQL = "SELECT TOP 100 PERCENT * FROM IncomeSMS WHERE smsTrackTIR Is Not Null"
    On Error Resume Next ' не останавливаемся на ошибках
    Set oRecordset = oConn.Execute(strSQL)
    If oConn.Errors.Count > 0 Then
          Dim E
          For Each E In oConn.Errors
            Debug.Print "При выполнении запроса возникла ошибка: " & E.Description
          Next
    End If
    Dim ListIndex: ListIndex = 3
    If oRecordset.EOF Then
        Debug.Print "нифига нету"
      Else
        Debug.Print "получены данные"
        Dim i
        For i = 1 To oRecordset.Fields.Count
            ' заполняем шапку
            Sheets(ListIndex).Cells(1, i).Value = oRecordset.Fields.Item(i-1).Name
        Next
        Dim j: j = 2
        Do Until oRecordset.EOF
            ' формат данных:
            For i = 1 To oRecordset.Fields.Count
                Select Case oRecordset.Fields.Item(i-1).Type
                    Case adInteger, adSmallInt
                        Sheets(ListIndex).Cells(j, i).NumberFormat = "0.00"
                    Case adDBTimeStamp
                        Sheets(ListIndex).Cells(j, i).NumberFormat = "m/d/yyyy"
                    Case Else
                        Sheets(ListIndex).Cells(j, i).NumberFormat = "@"
                End Select
                ' заполняем табличку:
                Sheets(ListIndex).Cells(j, i).Value = oRecordset.Fields.Item(i-1).Value
            Next
            oRecordset.MoveNext
            j = j + 1
        Loop
    End If
    ' выравниваем ширину колонок по данным
    Sheets(ListIndex).Cells.Select
    Sheets(ListIndex).Cells.EntireColumn.AutoFit
    Sheets(ListIndex).Range("A1").Select
    On Error GoTo 0
    oConn.Close
    Set oConn = Nothing
    Set oRecordset = Nothing
End Sub

P.S. не все обработки ошибок приведены, это уже добавите самостоятельно .
Правка 1: поправил индексы в циклах, вечно забываю где они от 0, а где от 1.

Правка-поправка 2: если будет возникать ошибка на константах типов данных(adInteger etc), то нужно либо добавить ссылку на ADO в референсах, либо в модуле прописать значения для используемых констант:

Option Explicit
Const adInteger = 3
Const adSmallInt = 2
Const adDBTimeStamp = 135

Для других типов данных(если вдруг понадобится) см. DataTypeEnum.