Creating an Import Spec – Advance button missing?

[Mike P]

I am using Access 2007 (SP1 MSO) and wish to create an Import Specification
for an Excel Spreadsheet so I may use a DoCmd.transfertext in my VB code.
However, when I start the Import Wizard under Create -> External Data ->
Excel, the Advance button does not show up; therefore, I am unable to Save
the import as a Import Spec.

When I use txt files the Advance button show up, but not on Excel import.
Any ideas here? This problem seems to be new, I could create them before.

Thanks,
Mike P

 

[Klatuu]

There has never been an Advanced button for importing Excel files. They are
only for text files.

 

[Mike P]

How do I create an Import Spec so I may import xls files via VB?

 

[Klatuu]

You can't define a spec for xls files, only for text files.
What difficulties are you having importing the xls?
In many cases, importing into a temporary file, then using queries to
massage the data into the format you need is useful. Other times, you may
want to link to the spreadsheet rather than import it and use append queries
to move the data into your tables like you want it.

 

[Mike P]

I have a program in which the user selects which xls file to import, which I
import into a tmp table, and work with the data via VB code. I have built
import spec's for other Excel imports, but it has been a year and believe I
build the import spec in Access 2003 prior to porting my program to Access
2007. Here is a copy of the code I use to pull in an xls file. This is
critical to do since I often import many files in a single subdirectory. it
is nice to have the process completely automated as I do and would like to
keep that. I just can't seem to create an import spec for xls.

' Load tmp table tblTmpPDate
DoCmd.TransferText acImportDelim, "my_Import_Specification", _
"tblTmpPrice", gstrImportFile

Thanks for your help here!

 

[Chris O'C via AccessMonster.com]

Convert the xls file to a csv file. Import the csv file and the advanced
button will show up in the import text wizard so you can create an import
spec.

Chris
Microsoft MVP

 

[Klatuu]

You must have been using csv files which look like and can be create from
Excel. There is not now nor has there ever been the ability to create an
import spec for Excel formatted files.