Originally Posted by ianverrier
Thanks for the response. I can't import the whole spreadsheet to a transitional table beacause it has too many fields.
Then Excel Automation code is the only way for you.
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim strTempPathAndFileName As String
Dim strPathAndFileName As String
Const xlToLeft As Long = -4159
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
strPathAndFileName = "C:\Somefolder\Somefile.xls"
strTempPathAndFileName = "C:\SomeOtherFolder\SomeOtherFileName.xls"
Set xlWB = objXL.Workbooks.Open(strPathAndFileName)
' saves the workbook as another file so we can delete unwanted columns
Set xlWB = objXL.Workbooks.Open(strTempPathAndFileName)
Set xlWS = xlWB.Worksheets("SheetNameHere")
' selects and removes the extraneous columns
Set objXL = Nothing
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", strTempPathAndFileName, True
If Dir(strTempPathAndFileName) <> "" Then
So this opens the workbook, then copies it, by using a save as, to another location and then deletes the unwanted columns (I didn't include any that might be after column CH so if so you will need to modify).
Then it will save the temp file, do the import, and then deletes the temp file.
Hopefully this will help.