How to import an Excel Spreadsheet without specific column(s)?

[jbt007]

Hi All,

I am using the following code to import a spreadsheet into my Access 2003 tblWeekly table:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblweekly", strOutFile, True, "impData"

The strOutFile contains the path and file name of the spreadsheet. The "impData" is the range that contains the data I need to import. It is a fluid range (changes from week to week). The spreadsheet is created using Access Automation (reading a txt file report and converts it to a spreadsheet). My user community has requested I add a specific field to the spreadsheet in a specific location, which is not a problem. The issues is that I then import the sheet into a table in the db. This added field will cause the data to break normalization rules as there is already a primary key field linked (one-to-many) to the added field data in the spreadsheet. So, I just want to ignore column "C" in the "impData" range.

I can't find any thread showing how to do this or if it's possible. I have not tested, but if the table field names match the field names in the spreadsheet, will it just be automatically omitted?

The only other thing I can think of would be to do one of the following:

1. - a. save the workbook, b. delete the column in question, c. import the sheet, and c. close the workbook without saving
2. - a. create a new sheet in the workbook, b. copy "impData" to the new sheet, c. Delete the column in question, d. name the range of data on the new sheet without the added column to "impDataReal", e. import "impDataReal", f. delete the added sheet.
3. - build the impData range omitting the column. Something like:
   

   With xlSht
       strRng = .Range(xlSht.Cells(1, 1), xlSht.Cells(2, 1058)).Address & "," & .Range(xlSht.Cells(4,  1058)).Address
   End With

   Then do the import with "strRng" vs "impData". - not sure how this would work either, because "strRng" would be "$A$1:$B$1058,$C$1:$C$1058" - Does the importsheet support non-continuous ranges?

Methods 1 & 2 above seem kind of "clunky" to me. I was hoping someone out there had a better way! I'll have to try #3, but something tells me Access isn't going to like it.

Thanks in advance...

[nico5038]

In general I would separate the import from the actual data to be processed.
So in a similar situation I've created a tblImport holding all columns and with an ImportSessionNumber to keep the imported files "separated".
Next I fill the actual table using a query selecting the proper columns from the tblImport for the highest ImportSessionNumber.
This has an additional advantage that (depending on the number of sessions you store) you can "go back" when some data wasn't OK or you want to review historical info.

Nic;o)