importing excel worksheet to Access table

[Jill Lin]

I still can't figure out what I am doing wrong in my code. I am writing a
code in Excel VBA where, when user press the button, the program will
automatically import the specified Excel spreadsheet to an Access table. I
use the DoCmd.TransferSpreadsheet function but I keep getting "Variable not
defined" error. Please help.

Here is my code.
Sub OpenAccess()
Dim oApp As Object
Dim LPath As String

LPath = "C:\folder\test.mdb"
Set oApp = CreateObject("Access.Application")
oApp.OpenCurrentDatabase LPath
oApp.DoCmd.TransferSpreadsheet acImport, _ acSpreadsheetTypeExcel9,
"table1", "C:\clients\CTim\Additional_Accounts.xls", True 'table1 is a
table in the test.mdb where I want the Excel file to be copied to.

Thanks,
Jill

url:http://www.ureader.com/gp/1052-1.aspx

 

[Ken Snell [MVP]]

EXCEL does not know the values of the ACCESS intrinsic concstants such as
acSpreadsheetTypeExcel9, Replace acSpreadsheetTypeExcel9 with the number 8:
oApp.DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "table1",
"C:\clients\CTim\Additional_Accounts.xls", True

 

[Ken Snell [MVP]]

Sorry, didn't correctly modify your code step:

oApp.DoCmd.TransferSpreadsheet acImport, _
8, "table1",
"C:\clients\CTim\Additional_Accounts.xls", True


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/