import data from excel

[tricks via AccessMonster.com]

I'm trying to import data from excel using vba code. The excel file has
three columns, name weight,and team name. If I just go to file, get external
data, import everything works great. If I use the code below, the only
information that is imported is team name. The information in the other two
columns aren't imported. Here is the code I'm using:

Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
strDirectory = Left(strInputFileName, _
InStrRev(strInputFileName, "\"))
strFileName = Dir(strDirectory & "*.xls")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Team_Roster", strInputFileName, True
Any help would be greatly appreciated.

 

[OssieMac]

I have experienced some strange results when importing from Excel. Am I
interpreting correctly in that it is the third column being imported and the
first 2 are ignored?

Some things to try.

Include the range to import like this (as a test at this point in time. Will
need to establish the actual range with code if this works.).

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Team_Roster", strInputFileName, True, "A1:C100"

Note: Sheet name can also be included.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Team_Roster", strInputFileName, True, "Sheet1!A1:C100"

If the above works and you need help with code to establish the actual range
to import each time then get back to me.

Also, if the table is being created with the import then try with the table
already created and delete the contents before the import. (Since it works
importing manually then create the initial table that way.)

DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * From [Team_Roster];")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Team_Roster", strInputFileName, True
DoCmd.SetWarnings True

Note: Should the above code fail after DoCmd.SetWarnings False and before
DoCmd.SetWarnings True then you need to run a little sub to restore the
warnings.