Improve ADO code, Export Excel to Access
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 |
Improve ADO code, Export Excel to Access
SteveC wrote ...
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. -- |
All times are GMT +1. The time now is 08:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com