DoCmd.TransferSpreadsheet - Skip columns

I use TransferSpreadsheet in VBA to import an Excel sheet into a table in MS Access. Right now, I would like to skip a number of columns in the import. When you use the import wizard in Access, you can choose to skip any number of columns anywhere in the spreadsheet. How can I get the same effect in the VBA statement? For example, if I have a spreadsheet that contains columns A - G and I want only columns B, C, and G, how would I get that done?

I know I am able to skip column A by giving a Range of "B:G" but I can't give up multiple ranges... But it should be possible, unless the wizard does additional work (which it doesn't seem to do anywhere else)... I hope.
LVL 10
NeoTeqAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
a second table to accept all the columns and append only the required field to the final destination table will be good.
0
 
Rey Obrero (Capricorn1)Commented:
how about importing columns A-G then delete unwanted fields?

you can also delete columns A,D,E,F from the ss (copy) before importing
0
 
Rey Obrero (Capricorn1)Commented:
another way is copy all the required columns in another sheet and import the whole sheet.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
NeoTeqAuthor Commented:
I'm not a huge fan of the editing of the sheet beforehand... I'd have to make a copy each time and delete that after the import because I can't edit the original, so I'd rather not do that. Anyway, deleting the fields isn't going to work either since I'll be importing into the same table every time.

I could create a second table in access containing the fields I need and create an insert query, but I was just wondering if selecting columns was possible programmatically, seeing as how it's possible manually.
0
 
NeoTeqAuthor Commented:
I really would've hoped for some alternatives that I didn't think of myself... But it appears there aren't any.
0
 
Rey Obrero (Capricorn1)Commented:
is this a one time  operation or recurring?
0
 
NeoTeqAuthor Commented:
Recurring... I went for a solution where I created an import table and a query from that to select what I need.
0
 
Rey Obrero (Capricorn1)Commented:
if you want to employ the import spec and the transfertext method,
you can save the xls file as csv, then import the csv

in a csv file you can indicate what field you want to skip via the import spec.

is this an option?
0
 
NeoTeqAuthor Commented:
Not really... You know how users will be more likely to make mistakes when you introduce another step.

Anyway, the solution I have now works :)
0
All Courses

From novice to tech pro — start learning today.