PDA

View Full Version : How can I link to a large table in another excel file ?



Els G
2014-06-24, 07:57 AM
The traditional copy in one excel workbook and paste in the other excel workbook does not work. We are talking about a huge table (37000 rows and 77 columns)
I want to create different excel files with reports for different target audiences based on this data in the master excel file.
Does anyone know how I can realize this?
Regards
Els

p45cal
2014-06-24, 11:26 AM
This sounds perfect for MS Query;
On the Data tab of the ribbon, in the Get External data section, choose From other sources, choose From Microsoft Query, in the resulting dialogue box choose Excel Files*, navigate to your data file, choose the table (and the columns you want to include from it) in your report, in the next steps choose how you want to filter and sort the report, and in the last step, choose whether to return the data to excel, or fine tune the query in MS Query. That's it.

The data file doesn't need to be open.

Els G
2014-06-24, 04:16 PM
Unfortunately I get the message "This datasource contains no visible tables" using MsQuery. I have already renamed both table and worksheet to "database", but this did not fix the problem neither

p45cal
2014-06-24, 07:58 PM
Don't you even get the sheet names appearing?
What version of
1. Excel are you using to try and grab the data?
2. what version of Excel is the datafile saved as?

I tried here with an Excel .xlsx file with just some data and headers in a sheet, no names, no Excel Tables, saved it. Then tried to MSQuery it from Excel 2010, it saw the headers straight away.

Els G
2014-06-27, 10:15 AM
I also use excel 2010

p45cal
2014-06-27, 01:45 PM
Well that's a surprise (that it's not working).
Perhaps we can do some remote assistance with the likes of TeamViewer?

Els G
2014-06-27, 03:59 PM
I have installed TeamViewer, how do we proceed from here

p45cal
2014-06-27, 05:50 PM
OH, right, will Private Message you here straight away.

p45cal
2014-07-03, 03:58 PM
Given up?