Using ADO to write data to an Excel file

[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

[NickHK]

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

NickHK

Post by 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...
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
. . .
Do While Not (oProdRS.EOF)
*** oRS.AddNew
oRS.Fields(0) = oProdRS.Fields("ProductName").Value
. . .
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

[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?

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

[Paul Clement]

On Tue, 28 Feb 2006 20:54:27 -0800, "nebbish" <***@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)

[nebbish]

Post by Paul Clement
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

[Paul Clement]

On Wed, 1 Mar 2006 11:01:33 -0800, "nebbish" <***@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)

[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

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