Hi All,
I want to export 5 Access Tables to a single excel workbook with 5 sheets
Hi All,
I want to export 5 Access Tables to a single excel workbook with 5 sheets
Hi,
a suggestion is that you link from Excel to each of the 5 Access tables (or queries if you like). On each sheet you create a link to the desired Access table/query using Excels function "MS Query".
Method (translated from Swedish, wording in English might not be 100% equal):
In Excel. Menu "Data / Import external data / New database query"
At the windows select "Data source" (tab "Databases"), select "MS Access Databases...". If there are two or more instances named "MS Access Databases..." try each of them. One is for Access 2003 and one is for a newer version(s) of Access. Unclick the box at the bottom an then click OK.
In the "Explorer-like-window", select you Access database + OK.
In next window. Select your table or query. Click "Add".
In the query window, now double click the * in the datatable.
Then click the icon "go out from door" (or the first Menu, option "Return data to Excel..."
You will not "pivot-table-like" be asked where to put the result. Selcet any destination and hit ok.
Now you should have the result in Excel on that sheet. Repeat on next sheet for next Access table.
Hint: Right click the output area in Excel, select "Properties for data area". Read and select according to your wish (update frequence, automatic update etc).
You can also access huge tables by creating a pivot table with a small aggregated result. Try creating a pivot table (Data / "...Create Pivot table..."). Select "External data source" in the first window of the wizard. Press next, select "Get data" (button) and the follow the instructions above.
You can also work with parameters in MS Query, let the user select a part number in a huge table and return all data for that record etc etc. MS query is far, far from Access, but extremenly powerful be giving Exceluser (that don't know Access), possibility to reach access data easily!
I hope this works out for you!![]()
Hi,
did this solve it for you?
Best regards
Bjorn