TransferSpreadsheet problem

  • Thread starter Thread starter Grace
  • Start date Start date
G

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.
 
Answered in .externaldata. Please don't post the same question
separately to multiple groups.
 
Back
Top