Import Excel Data using TransferSpreadsheet command

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

Grace

Hello, I am trying to import data from an excel
spreadsheet and then run an append query to add the data
to an existing table. When I run the code, it can't seem
to find the file. I am entering the path name correctly
(TrainerTimeTracking.xls)and the file does exist. I have
double checked the spelling of the filename.

Here is my code so far:

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

gotTable = False
dbPath = InputBox("Enter Location of Spreadsheet"
+ "(drive:\path\)", "Location of Spreadsheet")

If dbPath <> "" Then

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "TrainerTimeTracking.xls", True
MsgBox "Spreadsheet has been imported...",
vbExclamation, "Import Data"

DoCmd.OpenQuery "AppendTimeTrackingData", acNormal, acEdit
MsgBox "Data has been added to Time Tracking Table...",
vbExclamation, "Data Added"

gotTable = True

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 and updated
successfully...", vbExclamation, "Import Successful"
ImportExcel = True

End If

Else
MsgBox "Import has been canceled by user",
vbCritical, "Operation Canceled"
ImportExcel = False

End If

End Function

Your help is greatly appreciated.

Grace
 
Grace,

I think it's safe not tomess with the dbPath, try this instead:

Function ImportTrainingData()
Dim ssPath As String, sSheet as String
Dim gotTable As Boolean

gotTable = False
ssPath = InputBox("Enter Location of Spreadsheet"
+ "(drive:\path\)", "Location of Spreadsheet")

sSheet = ssPath & "TrainerTimeTracking.xls"

If ssPath <> "" Then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, sSheet, True
....
etc

I would take an extra step and check for the existence of the file (so as to
make sure the path AND file name are correct):

Function ImportTrainingData()
Dim ssPath As String, sSheet as String
Dim gotTable As Boolean

gotTable = False
ssPath = InputBox("Enter Location of Spreadsheet"
+ "(drive:\path\)", "Location of Spreadsheet")

sSheet = ssPath & "TrainerTimeTracking.xls"

If Dir(sSheet) <> "" Then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, sSheet, True
....
etc

HTH,
Nikos
 
Thank you Nikos,
I took your suggestions below and created the following
code:

Function ImportTrainingData()
Dim ssPath As String, sSheet As String
im gotTable As Boolean

gotTable = False
ssPath = InputBox("Enter location of Spreadsheet"
+ "(drive:\path\)", "Location of Spreadsheet")

sSheet = ssPath & "TimeTracking.xls"

If ssPath <> "" Then

DoCmd.TransferSpreadsheet acImport,
SpreadsheetTypeExcel97, "tblTrainerTimeTracking", sSheet,
True

etc...

It now seems to find the location and file, but is giving
me the following error message: Run-time
error '2391' "Field 'F8' doesn't exist in destination
table 'tblTrainerTimeTracking.'

Where is 'F8' coming from????

Thanks again for your help.

Grace
-----Original Message-----
Grace,

I think it's safe not tomess with the dbPath, try this instead:

Function ImportTrainingData()
Dim ssPath As String, sSheet as String
Dim gotTable As Boolean

gotTable = False
ssPath = InputBox("Enter Location of Spreadsheet"
+ "(drive:\path\)", "Location of Spreadsheet")

sSheet = ssPath & "TrainerTimeTracking.xls"

If ssPath <> "" Then

DoCmd.TransferSpreadsheet acImport,
Click to expand...
acSpreadsheetTypeExcel97, sSheet, True
....
etc

I would take an extra step and check for the existence of the file (so as to
make sure the path AND file name are correct):

Function ImportTrainingData()
Dim ssPath As String, sSheet as String
Dim gotTable As Boolean

gotTable = False
ssPath = InputBox("Enter Location of Spreadsheet"
+ "(drive:\path\)", "Location of Spreadsheet")

sSheet = ssPath & "TrainerTimeTracking.xls"

If Dir(sSheet) <> "" Then

DoCmd.TransferSpreadsheet acImport,
Click to expand...
acSpreadsheetTypeExcel97, sSheet, True
 
Grace,

Just guessing here, but worth checking:

Does tblTrainerTimeTracking has 7 fields? Does the spreadsheet have 7
columns? If yes it would be expected to work, but there is a small problem
in the spreadsheet, not in Access: there used to be some formulae or data in
column(s) beyond G in the Excel spreadsheet, which were cleared (Del button
or Clear Contents), so you don't see them, but the columns were not deleted
altogether, so Access still "sees" them and tries to import them. Open the
spreadsheet in Excel, select all columns beyond the last column with real
data and right click > Delete (instead of Clear Contents), then save and
close. It should work now.

HTH,
Nikos
 
THANK YOU! THANK YOU!! That worked. Strange but it did.

I knew I wasn't going crazy.

Have a great day.
 
Back
Top