access to connect two excel databases

[tomanton]

hi,

I have two databases in excel. Connecting them together with formulas makes process very slow, so as an option i'm thinking of linking one database to access and outputting it to excel, so the changes made in one database reflect in the other one?

i found on internet its possible, however could not find how. I made a linking excel table in access but cant export it to another workbook.

Any help is appreciated

Thanks

[ranman256]

you can link 1 excel file into access as a table. (or just import the data into a native table)
then you can export data to a 2nd excel file via docmd.transferspreadsheet

[dflak]

You can also eliminate the middle man (Access) there is a data import mechanism call MS-Query that can read data from just about any kind of data source including another Excel Spreadsheet. MS-Query can get the data without opening the other spreadsheet, and can be set up to refresh on open. It also takes SQL statements so you can select just the columns you want and filter just the rows you want.

[tomanton]

hey folks,

Thanks for your answers.

i will now have to look into these options and try them out

[alansidman]

As a follow up to dflak, here is a link on how to employ MS Query

http://exceluser.com/formulas/msquer...ional-data.htm

[tomanton]

As a follow up to dflak, here is a link on how to employ MS Query

http://exceluser.com/formulas/msquer...ional-data.htm

That's awesome thanks for this. I was trying to find some resources on how to do that but all I got was receiving an error from the Microsoft Query wizard when trying to create a query of an xlsx or xlsm file - " External table is not in the expected format." looking at the steps it's a completely different to the way I was doing, however I can see in here is about connecting old version too. Do you know if excel file types matter when using this method?
I will certainly try this

[dflak]

Alan, thanks. I tried to write an article to explain how to use MS-Query and it's OK but only after you are familiar with it. I've gotten too far away from the basics. I use it with Oracle databases and extremely complex queries that I work out using TOAD or WinSQL.

tomanton - I read from xlsm, xlsx and even xlsb files. I don't know if I have an old xls file to try it on. I'd have to see your source data to figure out what might be causing the error.