Master and Source Workbooks

[daveburke114]

Hi Guys,
I have a question that has probably been asked a million times before but after searching the forum I'm non the wiser!

I have (for example) 3 Source workbooks and 1 master workbook.
On each Source workbook is a worksheet with data I need putting onto its own Worksheet in the Master Workbook.

I would like the Master workbook (and therefore all worksheets within) to update automatically when the Source Workbooks are updated.

I understand this can be done with a 'Paste Link' but I seem to be doing something wrong as either:
1) I have to select the whole sheet and that also copies all empty cells/takes up a lot of memory. Or
2) I have to select a specific amount of cells, which is not appropriate as the Source Workbooks are always growing.

Should I be able to select a named Range of cells from a Source worksheet? and would this named range grow as the Source database grows?

Also, ideally, I would like to just open the Master workbook and see all the updated sheets, without individually opening each other Source workbook, as potentially there could be more than 10.

Is it possible to update all the Master worksheets from closed Source workbooks? Or at a push, could someone explain how to code some VBA to open and close all Source workbooks when the Master is opened?!

I hope that all makes sense!

Regards
Dave

 

[GlennUK]

How is the data organised within the Source workbooks? If the data is in tables then MS Query will be able to pull the data in, regardless of the number of records.

 

[daveburke114]

hi GlennUK,
Thanks for your reply

At the minute we are just starting out and the source workbooks are produced in a variety of ways. Please excuse my ignorance, I am not new to excel and know how to do alot of things, but don't use it often enough to know all the terms! Would a Table be:
Cell A1 Title, Cell B1 Name, Cell C1 Surname... etc
Cell A2 Mr, Cell B2 John, Cell C3 Jones, etc

If thats the case, then yes all the souce worksheets are Tables

Source 1;
Exported via a booking system (its for a hotel), this is arranged into a title row, then all the data in rows underneath

Source 2;
Data entered manually, this is arranged into a title row, then all the data in rows underneath. Some of these cells are selected via drop down menu.

Source 3;
Data entered manually, this is arranged into a title row, then all the data in rows underneath. Some of these cells are selected via drop down menu.

Some of the Source workbooks are exported from other programs as .csv


If this all sounds like it should work, how would I go about sorting the MS Query?

As the source files are .XLSX files, I have tried (in the master workbook):
Data>From Other Sources>From Microsoft Query>Excel Files*>OK
and get the following error
'The data source contains no visible tables'

Or should I be using
Data>From Other Sources>From Microsoft Query>New Data Source
then which settings should I be using?!

Thanks for your help
Dave

 

[GlennUK]

Is this any help:

http://www.tek-tips.com/faqs.cfm?fid=5829

?

 

[daveburke114]

Hi GlennUk, yes thank you very much that worked great from all the sources!

I still got 'no tables' error but added them manually and its working now.

When I open the Master workbook though, it doesn't update the data until I click 'Refresh', is there a way to get it load the new data on opening?

 

[GlennUK]

Do a macro recording of yourself doing the refresh.

Then put that code inside the Workbook_Open event code area.

To do that ... if you activate the VBA editor, and use the Project Explorer to show your workbook objects, then right click the ThisWorkBook object and choose View Code.

You'll get 2 drop-downs across the top of the code window, choose ThisWorkbook in the left one, and Open in the right one ( although Open should actually appear and be the default and show a code structure for Workbook_Open automatically, anyway ).

 

[daveburke114]

ok thanks mate I will try that (havn't used macros in a while so will have a play lol)

 

[daveburke114]

For anyone else reading this and getting the 'The data source contains no visible tables' this is what I did:

In the MASTER WORKBOOK
Data>From Other Sources>From Microsoft Query>Excel Files*>OK
Then Select your SOURCE WORKBOOK from the directories menu
You may get the error above
Click Options, and make sure all the boxes are ticked.
Your worksheets should show up in the list, select the one you need press the little '>' button
Click NEXT
If you wish to use all the column headings just select NEXT
Select any sorting you may wish to use, or click NEXT
Then select: 'Return Data to Microsoft Office Excel' and click OK
This should take you back to your MASTER Workbook
Select the place on the worksheet you wish to place the data then click OK

 

[GlennUK]

For anyone else reading this and getting the 'The data source contains no visible tables' this is what I did:

In the MASTER WORKBOOK
Data>From Other Sources>From Microsoft Query>Excel Files*>OK
Then Select your SOURCE WORKBOOK from the directories menu
You may get the error above
Click Options, and make sure all the boxes are ticked.
Your worksheets should show up in the list, select the one you need press the little '>' button
Click NEXT
If you wish to use all the column headings just select NEXT
Select any sorting you may wish to use, or click NEXT
Then select: 'Return Data to Microsoft Office Excel' and click OK
This should take you back to your MASTER Workbook
Select the place on the worksheet you wish to place the data then click OK

Thanks ... useful stuff