Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
How to improve this code? | Excel Programming | |||
vb code to export data into existing Access table | Excel Programming | |||
Set parameters for JPEG export by VBA (to improve quality) | Excel Programming |