PDA

View Full Version : Solved: pull excel workbook into excel template



pdeshazier
07-21-2009, 12:44 PM
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....:doh:

grichey
07-21-2009, 01:54 PM
export access as csv and import in excel as delmited text?

p45cal
07-21-2009, 02:45 PM
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
07-21-2009, 02:50 PM
i don't have those options.... i have 2007, do you?

p45cal
07-21-2009, 02:54 PM
2003.
Surely they've got to be there somewhere?!

pdeshazier
07-21-2009, 02:59 PM
found them!!! thanks!!! working

pdeshazier
07-21-2009, 03:00 PM
how do i mark as solved?

Aussiebear
07-21-2009, 06:12 PM
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
07-22-2009, 06:47 AM
to take this farther, can i write a macro which will do this automatically?

p45cal
07-22-2009, 07:26 AM
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.