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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rey Obrero (Capricorn1)Commented:
a second table to accept all the columns and append only the required field to the final destination table will be good.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.