Solved: pull excel workbook into excel template

[pdeshazier]

from an access database designed by a vendor of ours, we run datasheets then export to excel. I need to "load" this exported excel file into an excel template. how do i do that? under 'get external data' there's no choice for getting another excel file....

[grichey]

export access as csv and import in excel as delmited text?

[p45cal]

Yes there is.
Data|Import Extrernal Data|New Database Query
On the Databases tab select '<New Data Source>' (unless you can already see something to do with Excel) and click OK.
In the next dialogue, for field 1 enter a name such as 'Excel Files'
For field 2 choose 'Microsoft Excel Driver (*.xls)'
Click 'Connect' button
Follow instructions.
Thereafter you should see Excel files in the first dialogue box.
You'll be able to connect to whole sheets and named ranges in the closed workbook.

[pdeshazier]

i don't have those options.... i have 2007, do you?

[p45cal]

2003.
Surely they've got to be there somewhere?!

[pdeshazier]

found them!!! thanks!!! working

[pdeshazier]

how do i mark as solved?

[Aussiebear]

HI Pam, You should find this option under the Thread tools ( just above your initial post. I've done it for you this time.

[pdeshazier]

to take this farther, can i write a macro which will do this automatically?

[p45cal]

A macro could be written to do this automatically but it needs knowledge of what you're querying (varying filenames?). You can set up querytables in excel, even include SQL statements. It gets quite involved. I did it for one company I worked for - evolving it into a direct query on the Access database. It depends on how often you have to do it, or how quickly you need to get the information, as to whether it's worth doing automatically.