Importing from Excel

[lovemuscle2005]

I have a regular import from excel going into an access table.

My problem is that the excel file has more culumns in than I want to import, so I want to be able to choose which, i.e. A,B,E,G,H.

Is there a way anyone knows?

Thanks in advance

 

[me2]

When you use the import wizzard, there is a check box for "do not import this field" (about 3 pages in on the wizzard). You can highlight a field and the click to not import that field. Just move to through each field before you click "next" on the wizzard.

me2

 

[lovemuscle2005]

Thats great but I'm trying to automate it for non access users!

 

[Norie]

You could create an import specification that imports the fields that you want as the data type you want.

This can then be used in code.

Goto Advanced... in the Import Wizard when you manually do the import.

 

[lovemuscle2005]

Thanks for replying, could you tell me a little more please?

Thanks

 

[Norie]

Well goto File>Get External Data... and select the Excel file.

Follow the steps to import the required columns only.

Before you hit finish click Advanced...

Now make any other adjustments to the import.

Then save the specification with a relevant name.

You should then be able use this specification when importing using code.

I can't access Access at the moment so can supply the syntax but you should find it in the help file.

 

[Gates Is Antichrist]

Use a named range in Excel and only import that - don't import a worksheet. Non-contiguous ranges are allowed in Excel (e.g. columns A,B,E,G,H only). If you're queasy doing that, you can construct a redundant set of the data in a rectangle - e.g. insert a new sheet where A1=Sheet1!A1, B1=Sheet1!B1, C1=Sheet1!E1, etc. and again define a range appropriately.

You can also link to Excel instead of importing - again, directly to a named range. Linking would be one-time-only vs. importing every time the data changes, if that fits your scenario. You said it is a "regular import" so I thought I'd mention this.