Using ADO to write data to an Excel file

1 Mar 2006 4:54 AM

nebbish

i'm using VB6 example code from microsoft that produces a standard exe
project which connects to an mdb file to get the data and connects to an xls
file to write the data (at the end of this post i've included the url of the
microsoft article that has the code).

now the example works just fine as is.....BUT....when i try to put the code
in a VBA project (i'm using VBA from within Access) i get an 0x80040e09 error
when ever i call .AddNew on the recordset that was opened against the excel
file.

First my question - why doesn't the Jet Excel engine work differently in VBA
compared to VB6 - and secondly is there *ANY* other way to perform recordset
updates (in VBA) when the recordset is opened against an excel file.

thanks in advance for any thoughts, answers, comments...

Now some explanation of what i did:
i created a new Module (left it called 'Module1') and pasted ALL the code
from Form1.frm.

the sample code basically consists of 5 button click handlers and a
form_load handler. form_load only initialized some global variables, so when
i transfered the code into VBA i removed that function and turned the global
variables into global constants.

then i added another global constant called MyAppPath which i used to
simulate "App.Path" which is used all over in the example code - i simple
initialized this new constant with the path where i unzipped the example code.

lastly i just commented out all the calls to ShellExecute - they only bring
up the results anyway.

then to run the code, since it is VBA, i just put the cursor in the handler
named 'cmdSample3_Click' and hit F5

i get the error on the following line:

          .           .          .
        Do While Not (oProdRS.EOF)
***        oRS.AddNew
              oRS.Fields(0) = oProdRS.Fields("ProductName").Value
          .           .          .

the example code came from the following microsoft article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

which is entitled "ExcelADO demonstrates how to use ADO to read and write
data in Excel workbooks" - just in case the link doesn't work googling a
couple of words from the title should work.

thanks again,
nebbish

1 Mar 2006 5:42 AM

NickHK

nebbish,
Can you not just link the Excel file in Access, and leave the business
internally to Access/Jet/ADO.

NickHK

Show quoteHide quote

"nebbish" <nebb***@discussions.microsoft.com> wrote in message
news:9164561C-4946-4786-A176-FEB6DFE6B9B9@microsoft.com...
> i'm using VB6 example code from microsoft that produces a standard exe
> project which connects to an mdb file to get the data and connects to an
xls
> file to write the data (at the end of this post i've included the url of
the
> microsoft article that has the code).
>
> now the example works just fine as is.....BUT....when i try to put the
code
> in a VBA project (i'm using VBA from within Access) i get an 0x80040e09
error
> when ever i call .AddNew on the recordset that was opened against the
excel
> file.
>
> First my question - why doesn't the Jet Excel engine work differently in
VBA
> compared to VB6 - and secondly is there *ANY* other way to perform
recordset
> updates (in VBA) when the recordset is opened against an excel file.
>
>
> thanks in advance for any thoughts, answers, comments...
>
>
> Now some explanation of what i did:
> i created a new Module (left it called 'Module1') and pasted ALL the code
> from Form1.frm.
>
> the sample code basically consists of 5 button click handlers and a
> form_load handler. form_load only initialized some global variables, so
when
> i transfered the code into VBA i removed that function and turned the
global
> variables into global constants.
>
> then i added another global constant called MyAppPath which i used to
> simulate "App.Path" which is used all over in the example code - i simple
> initialized this new constant with the path where i unzipped the example
code.
>
> lastly i just commented out all the calls to ShellExecute - they only
bring
> up the results anyway.
>
> then to run the code, since it is VBA, i just put the cursor in the
handler
> named 'cmdSample3_Click' and hit F5
>
> i get the error on the following line:
>
>           .           .          .
>         Do While Not (oProdRS.EOF)
> ***        oRS.AddNew
>               oRS.Fields(0) = oProdRS.Fields("ProductName").Value
>           .           .          .
>
>
> the example code came from the following microsoft article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;278973
>
> which is entitled "ExcelADO demonstrates how to use ADO to read and write
> data in Excel workbooks" - just in case the link doesn't work googling a
> couple of words from the title should work.
>
>
> thanks again,
> nebbish
>

1 Mar 2006 7:01 PM

nebbish

i'm not entirely sure what you mean but i'm guessing it's kind of like linked
tables from other mdb files.

but as far as my scenario goes, i don't think that would be practical
because right before i open the excel file for writing, the user is presented
with a common open file dialog to choose the file that should get updated.

also i've never programmatically linked tables or unlinked them. is this
kind of what you mean?

Show quoteHide quote

"NickHK" wrote:

> nebbish,
> Can you not just link the Excel file in Access, and leave the business
> internally to Access/Jet/ADO.
>
> NickHK
>

1 Mar 2006 3:41 PM

Paul Clement

On Tue, 28 Feb 2006 20:54:27 -0800, "nebbish" <nebb***@discussions.microsoft.com> wrote:

¤ i'm using VB6 example code from microsoft that produces a standard exe
¤ project which connects to an mdb file to get the data and connects to an xls
¤ file to write the data (at the end of this post i've included the url of the
¤ microsoft article that has the code).
¤
¤ now the example works just fine as is.....BUT....when i try to put the code
¤ in a VBA project (i'm using VBA from within Access) i get an 0x80040e09 error
¤ when ever i call .AddNew on the recordset that was opened against the excel
¤ file.
¤
¤ First my question - why doesn't the Jet Excel engine work differently in VBA
¤ compared to VB6 - and secondly is there *ANY* other way to perform recordset
¤ updates (in VBA) when the recordset is opened against an excel file.

Can you post an example of the connection string you are using?

Paul
~~~~
Microsoft MVP (Visual Basic)

1 Mar 2006 7:01 PM

nebbish

"Paul Clement" wrote:
>
> Can you post an example of the connection string you are using?
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>

ok i've tried a few and here's the one i've been using most of the time
(fullpath is a variable that might as well be set to "C:\test.xls")

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    With conn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
        .Properties("Data Source") = fullpath
        .Properties("Extended Properties") = "Excel 8.0;HDR=NO;"
        .Mode = adModeReadWrite
        .Open
    End With

-or-     without setting '.Mode'

-or-

        .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & fullpath & ";" & _
              "Extended Properties=""Excel 8.0;HDR=NO;"""

-or-

        with the IMEX extended property included:

        .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & fullpath & ";" & _
              "Extended Properties=""Excel 8.0;IMEX=2;HDR=NO;"""

-or-    with    HDR=YES

i've tried these mostly.

and they are just modifications of the connection string used in Microsofts
example code.
the url is: http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

2 Mar 2006 2:35 PM

Paul Clement

On Wed, 1 Mar 2006 11:01:33 -0800, "nebbish" <nebb***@discussions.microsoft.com> wrote:

¤ "Paul Clement" wrote:
¤ >

¤ > Can you post an example of the connection string you are using?
¤ >
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤ >
¤
¤ ok i've tried a few and here's the one i've been using most of the time
¤ (fullpath is a variable that might as well be set to "C:\test.xls")
¤
¤     Dim conn As ADODB.Connection
¤     Set conn = New ADODB.Connection
¤     With conn
¤         .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
¤         .Properties("Data Source") = fullpath
¤         .Properties("Extended Properties") = "Excel 8.0;HDR=NO;"
¤         .Mode = adModeReadWrite
¤         .Open
¤     End With
¤
¤ -or-     without setting '.Mode'
¤
¤ -or-
¤
¤         .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤               "Data Source=" & fullpath & ";" & _
¤               "Extended Properties=""Excel 8.0;HDR=NO;"""
¤
¤ -or-
¤
¤         with the IMEX extended property included:
¤
¤         .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤               "Data Source=" & fullpath & ";" & _
¤               "Extended Properties=""Excel 8.0;IMEX=2;HDR=NO;"""
¤
¤ -or-    with    HDR=YES
¤
¤ i've tried these mostly.
¤
¤ and they are just modifications of the connection string used in Microsofts
¤ example code.
¤ the url is: http://support.microsoft.com/default.aspx?scid=kb;en-us;278973
¤

OK, I don't see any issue with the connection strings. What is the cursor type of your Recordset?

Paul
~~~~
Microsoft MVP (Visual Basic)

7 Mar 2006 6:15 PM

nebbish

my call to the ....oh hell, here is my entire function:

Public Sub WriteXlsFileADO(rs As ADODB.Recordset, fullpath As String, table
As String)
On Error GoTo WriteXlsFileADOError

    If Dir(fullpath) <> "" Then Kill fullpath

    Dim sql As String
    Dim fld As ADODB.Field
    Dim rsXls As ADODB.Recordset
    Dim conn As ADODB.Connection

    Set conn = New ADODB.Connection
    With conn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
        .Properties("Data Source") = fullpath
        .Properties("Extended Properties") = "Excel 8.0;HDR=YES;"
        .Mode = adModeReadWrite
        .Open
    End With

    sql = "CREATE TABLE " & table & " ("
    For Each fld In rs.Fields
        sql = sql & GetADOFieldDesc(fld) & ", "
    Next
    sql = Left(sql, Len(sql) - 2) & ");"
    Call conn.Execute(sql)

    Set rsXls = New ADODB.Recordset
    rsXls.Open "Select * from " & table, conn, adOpenKeyset, adLockOptimistic

    If Not (rs.bof And rs.EOF) Then
        rs.MoveFirst
        Do While Not rs.EOF And Not rs.bof
            rsXls.AddNew
            For Each fld In rs.Fields
                rsXls(fld.name) = rs(fld.name)
            Next
            rs.MoveNext
        Loop
        rsXls.Update
    End If

WriteXlsFileADODone:
    If Not rsXls Is Nothing Then
        rsXls.Close
        Set rsXls = Nothing
    End If
    If Not conn Is Nothing Then
        conn.Close
        Set conn = Nothing
    End If
    Exit Sub
WriteXlsFileADOError:
    err.Description = "ERROR in WriteXlsFileADO" & vbCrLf & _
                      " fullpath = " & fullpath & vbCrLf & _
                      "    table = " & table & vbCrLf & _
                      "      err = " & err.Description & vbCrLf
    err.Raise err.Number
End Sub

Show quoteHide quote

>
> OK, I don't see any issue with the connection strings. What is the cursor type of your Recordset?
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>