Forum Discussion
Feeding information automatically through to new work books.
So the first trick is to get MSQuery to recognise a Table and you have to go through these odd steps:
1. Highlight all but the last row of your Master Data set
2. In the NameBox just above column A, type MasterData and press Enter
Then you need to edit this range to include the last row
So 1. Go to Formulas > Name Manager
2. Change the reference for the name to include the last row
Save and close the Master File
In your destination file go to
Data > Other Sources > Microsoft query > Excel and connect to the Master File
Then see the image below for the things to click
Note. If you ever refresh the query when the master file is open it doesn't work. If it's open on someone else's machine it will open up a new copy and again not work.
Other than that it should work nicely
Power Query is Excel 2010+ is way easier!!
Given you're on Excel 2007 you could use Microsoft Query in Excel to tap into the Master File and bring the data into separate files and automatically pull new rows of data into the separate file.
This is especially true if you set up your Master file data in Tables (Ctrl+T)
There are a few steps involved but in essence you use Data > From Other Sources > From Microsoft Query > Excel
Give it a go and let us know how you get on
Hi Wyn,
I managed to work it out with the help of my son, we used the connections facility under the data tab.
It is perfect - we just have to refresh the connection in the target workbook every time we make a change in the source work book.
The only trouble is that it didn't copy accross all the headings - I am not using the table style in my source workbook - but the target workbook automatically comes through as a table. If I used the table style in my source workbook would this make all the headings come across?
Thanks for your input!
Andrea
I justed tested the scenario with the source workbook in table format and used the connections facility.
Same result - not all the headings came across.
In the source document, when I converted it to a table, all the columns that were very narrow expanded to fit the heading description.
When I made the connection and imported the data, SOME of the columns that were narrow stayed narrow and had no heading whereas some others that were narrow became extra wide (wider than the description) and retained the headings.
All very puzzling, but I would like to fix this as I don't want to edit the headings every time I refresh the data.
Thanks again for your input.
Andrea
Hi Andrea
Using a Table will llow you to pull the headings through, however, I forgot that there are a couple of extra things you need to do.
I'll pull together some screenshots tonight and post them here
Thank you so much Wyn!