TransferSpreadsheet / Specific Columns

[Vel]

I need to design a module to import specific columns of data from a
spreadsheet. The spreadsheet is generated by an agency my company contracts
with and contains several columns of data which are irrelevant for our use.
Is there a way to specify specific columns of data in the "range" argument of
the TransferSpreadsheet method?

I know I could just alter the spreadsheet, or create named ranges, but this
is an inelegant solution, and may cause some confusion considering the
technical expertise of my userbase. I need to be able to just have them
download the excel report (which will be different each week) then click a
button in my database. I'm very familiar with VBA code, but I'm not sure how
to reference specific columns in Excel for the data import.

 

[Jerry Whittle]

I suggest importing the entire spreadsheet into a temporary table within the
database. Then use update/append queries based on the temp table to populate
other tables as required. Then truncate or delete all the records out of the
temp table if necessary.

 

[Dirk Goldgar]

I suggest importing the entire spreadsheet into a temporary table within
the
database.

Or link to it, and execute append queries that draw data from the desired
columns in the linked table.

 

[Vel]

Thanks,

Its funny how sometimes the most obvious and simple answer can be overlooked
when trying out something new (like using the TransferSpreadsheet method for
the first time). I have to run various queries on the data once its imported
anyway before it reaches its final state and makes it into the permanent
table. I can just ignore those fields which I don't need.

 

[SlonicK]

Hi!
Try "A:A" or "Sheet1!A:A" for one column.
"A:Z" for 26 columns.