"Range" values

[Ray]

I am importing data from an excel spreadsheet into an Access table. I only
need the data from columns A, C, and E. If I set the range to A1;E20 I get
data from columns B and D also. How do I write the range so it only imports
A, C and E?

 

[Steve Schapel]

Ray,

I think in Excel you can select columns A, C, & E, holding down the Ctrl
key, and then name the range under the Insert=>Name menu. I have never
tried it, so I am not sure, but I think you can then use that range name
in your import.

 

[Ray]

Would that work for hundreds of different excel files or do I have to do it
for each file?

 

[Steve Schapel]

Ray,

You would need to do it for each file.

It would be possible to use a VBA macro in Excel to automate the
creation of an uncontiguous range like this. You may get some help with
this in an Excel newsgroup.

Alternatively, it may be worth looking at using a VBA procedure in
Access to use Office Automation to read the data out of the Excel files,
or alternatively link to the Excel files. These are all obviously
non-trivial approaches, but may be what is called for in your scenario.

 

[Ray]

I will try putting the following into my code tomorrow

=Sheet1!$A$1:$A$18,Sheet1!$C$1:$C$18,Sheet1!$E$1:$E$18

I have no issues getting from one file to another in my code and I can use
the range I mentioned in my first post and then "null" the B and D columns in
my table, but I would prefer to get just the data I need.