Importing Excel files

[newbie2181]

Hi,
I am using a very in-flexible piece of software that produces a report in an xls file. I want to automate importing this file into a database. I have tried to set up an import spec but i am finding that i don't have the advanced options availible to me because i'm using an XLS file. Is there any way around this problem

Thanks a lot

Dan

 

[glalsop]

Have a look at the TransferSpreadsheet method in Access help. It is reasonably flexible in the sense that you can choose to import particular ranges, etc.

-Gary

 

[phil009]

newbie 2181- If you still need help just tell us your .xls file name (and the range on the file if applicable) and we can walk you through it.

Phil

 

[MaxEd]

Here's another suggestion is to have to build some code to search the folder for the files you are looking for and then import it using the transfertext method. Here's an example.

DoCmd.TransferText acImportDelim, <specification>, <table>, <file with full path>, False


The specification contains the specs about the fields in your xls file if you do not have any column headings. It's easy to build one based on how each of your fields are formatted in the xls file.

-Laughter works miracles.

 

[newbie2181]

Hi,
Sorry i haven't replied to your replies sooner but i have been away. I have tried using TransferText but do i not need to specify a specification file to tell access how to import the data? The problem being that i can't create a specification file for an XLS file? Probably wrong here but if any one can help me out here it would be great!!

Thanks

Dan

 

[glalsop]

And what problems did you experience with the TransferSpreadsheet method?


-Gary

 

[newbie2181]

I can't create the specification file to tell access how to import the data

 

[MaxEd]

newbie2181,

If you can manually import the data into the table then you should be able to export the specifications as well. Then what you do is just use the same specification for importing.

 

[newbie2181]

how do i export the settings then i don't get the advanced button that you normally get?!