importing excel file into Access as a Table 1

[kingz2000]

Hi,

I am trying to import an excel file into an Access Mdb. I commence with the following code:

Dim oWkb As Excel.Workbook
Dim oWSht As Excel.Worksheet
Dim i As Integer
Dim importWB As Workbook

Set oApp = CreateObject("excel.application")
Set oWkb = oApp.Workbooks.Open("C:\Dokumente und Einstellungen\H84461\BBRIAccountPRM.xls")
Set oWSht = oWkb.Worksheets("_result_bbRIAccountPRM")

DoCmd.SetWarnings False

While oWSht.Range("A" & i).Value <> ""


but how do I actually import the data into a new table in the Acces.mdb????


Thanks in advance

 

[RivetHed]

Check out the TransferSpreadsheet method.

 

[kingz2000]

yes, thanks! thant works well for both importing and exporting.

However, what if I want to import into the same excel table, but in different tabs..the following code just gives me the 2 exports on the same tab.. I don't seem to find the property which enables me to export the 2 queries onto 2 tabs.
Private Sub Befehl0_Click()
Dim oApp As Excel.Application
Dim oWkb As Excel.Workbook
Dim oWSht As Excel.Worksheet
Dim i As Integer
Dim importWB As Workbook

Set oApp = CreateObject("excel.application")
Set oWkb = oApp.Workbooks.Open("C:\Dokumente und Einstellungen\H84461\BBRIAccountPRM.xls")
Set oWSht = oWkb.Worksheets("_result_bbRIAccountPRM")

DoCmd.SetWarnings False

'import
DoCmd.TransferSpreadsheet , 0, "ABC", "C:\Dokumente und Einstellungen\H84461\BBRIAccountPRM.xls", True

'export
DoCmd.TransferSpreadsheet acExport, 0, "C1_COMPLETE", "C:\Dokumente und Einstellungen\H84461\BABC2.xls", True
DoCmd.TransferSpreadsheet acExport, 0, "C1_PREMIUM", "C:\Dokumente und Einstellungen\H84461\BABC2.xls", True


DoCmd.SetWarnings True
End Sub


Thanks for any help.

kingsley

 

[PWise]

try this

with currentdb
.Execute "SELECT C1_COMPLETE.* INTO C1_COMPLETE IN ''  [Excel 8.0;imex=0;DATABASE=C:\Dokumente und Einstellungen\H84461\BABC2.xls] FROM C1_COMPLETE;"
.Execute "SELECT C1_PREMIUM.* INTO C1_PREMIUMIN ''  [Excel 8.0;imex=0;DATABASE=C:\Dokumente und Einstellungen\H84461\BABC2.xls] FROM C1_PREMIUM;"
end with