Improve ADO code, Export Excel to Access

[Guest]

Hi,
I have one Excel workbook with about 25 worksheets. In each worksheet column labels are in row 1 and data fills rows 2 through row 435.

The workbook is named MarginFormatted.xls and each worksheet name ends with the letters "EP."

The Access database is called MarginData.mdb.

How can I get ADO to pull all my excel worksheet data into Access? I've tried adjusting KeepItCool's macro (see below) but it doesn't work. The hour glass runs for a split second and then stops. No errors. But no data is imported into Access.

Thanks
Steve


Option Explicit
'Code needs a reference to Microsoft ActiveX Data Objects 2.5 or higher)

Const PATH = "C:\Documents and Settings\MyFolder"

Sub MDBDropDump()
Dim cnn As ADODB.Connection
Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PATH & "\MarginData.mdb" & ";"

Set cnn = New Connection
cnn.Open cnnString
cnn.CursorLocation = adUseClient
On Error Resume Next
cnn.Execute ("DROP TABLE DUMP")
cnn.Close

End Sub


Sub XLS2MDB(sXlFile$)
Dim cnn As ADODB.Connection

Const cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0;Header:YES"";Data Source=FormattedMargin.xls;"

Set cnn = New Connection
cnn.Open (Replace(cnnString, sXlFile))
cnn.CursorLocation = adUseClient
On Error GoTo errH
cnn.Execute "INSERT INTO dump IN 'C:\Documents and Settings\MyFolder\MarginData.mdb' select '" & _
Left(sXlFile, Len(sXlFile) - 4) & "' as Client , * from `*EP$`"

endH:
cnn.Close
Exit Sub

errH:
If Err.Number = -2147217865 Then
'This creates the table..
cnn.Execute "select '" & Left(sXlFile, Len(sXlFile) - 4) & _
"' as Client , * INTO dump IN 'C:\Documents and Settings\MyFolder’ from `*EP$`"
Else
MsgBox Err.Number & vbNewLine & Err.Description
End If
GoTo endH

End Sub

 

[Jamie Collins]

How can I get ADO to pull all my excel worksheet data
into Access? I've tried adjusting KeepItCool's macro
(see below) but it doesn't work. The hour glass runs
for a split second and then stops. No errors. But no data is imported into Access.

I see:

DROP TABLE DUMP
and

INSERT INTO dump ...

but no

CREATE TABLE DUMP ...

And you don't seem to be calling sub procedure XLS2MDB anyhow.

Jamie.

--