Importing Excel using DoCmd.TransferSpreadsheet

[Grace]

Hello,
I would like to import data from an excel spreadsheet and
append the data to an existing access table. Here is the
code that I have so far:

Function ImportExcel()
Dim dbPath As String
Dim gotTable As Boolean

gotTable = False
dbPath = InputBox("Enter Location of Spreadsheet" + Chr
(13) + "(drive:\path\)", "Location of Spreadsheet")
If dbPath <> "" Then
If Dir(dbPath + "LegalFactorsCapEarly.xls", vbNormal)
<> "" Then
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "ExcelLegalFactorsCapClosing",
dbpath + "LegalFactorsCapEarly.xls",, True
DoCmd.OpenQuery "ECA Append ECAData", acNormal, acEdit
DoCmd.DeleteObject acTable, "Excel LegalFactorsCapClosing"
MsgBox "Spreadsheet has been imported...",
vbExclamation, "Import Data"
gotTable = True
End If

If gotTable = False Then
MsgBox "No data was imported, please check
your path...", vbCritical, "Import Tables Fail"
ImportExcel = False
Else
MsgBox "Your data has been imported...",
vbExclamation, "Import Successful"
ImportExcel = True
End If

I am able to compile the code without problem, but when i
run the code it can not seem to find the table. I am
getting getting the "No data was imported ..." error. So,
it can't seem to find the data to import.

Also, I am using Excel version 2002. Would that make a
difference?

Your commnets are greatly appreciated.

 

[John Nurick]

Hi Grace,

Have you tried stepping through the code line by line using the
debugging toolbar, to see just where it goes wrong?

 

[John Nurick]

PS: There appear to be at least two errors in the code you posted: a
missing End If and the names "ExcelLegalFactorsCapClosing" and
"Excel LegalFactorsCapClosing".