Update Multiple excel workbooks from one workbook (1 Viewer)

[awade]

Good Evening All,
I currently have a master workbook, that has data uploaded to it every day from another location. The data in the master workbook then has to be transferred manually to 58 other workbooks located in their own folders. (cut and paste)

I have used a {=INDEX formula in each cell to transfer the data but it makes excel extremely slow, and sometimes excel stops responding.

The master workbook has a column that has a serial number listed in it and that serial number corresponds to the number of one of the 58 workbooks.

Is there a way using VBA to automatically update these other 58 workbooks without overwriting the data that is already in there, with the data that comes into the master workbook each day?

Thanks in advance

awade

 

[Rx_]

Yes there is. It is going to be some fairly heavy coding.
This is a case where Excel has become a Database.
You are basically creating 58 tables (each a workbook) and creating the equal to a SQL Update Query.

The maintenance of the code for a project like this begins to take on a life of its own.

So, before we embark on an epic Excel VBA solution, could you consider an Access solution where Access updates each Excel Workbook? Of course, we need to know more details.

For example: Do each of the 58 Workbooks get individually updated by the end users and then you only want the Master to update records based on the user input too?
Or, does the Master simply Replicate changes to each of the 58 based on some profile in each of the 58 workbooks.
e.g. Publisher - Subscriber vs Publisher to Edited Subscriber?

Just a quick for example.
The Master could become an Access table.
The 58 Workbooks could each have a mirrored Access table.
The Master could update each of the 58 Access tables according to some business rule filter.
Each of the individual 58 Tables could then use the same VBA code to destory the existing copy of Excel and replace it (at its designated location) with the new data from the Access individual table.
In reality, the 58 Tables might be one large table with 58 fields of what is sent to each individual 58 Workbooks. The 58 tables are just a mental model to use.

If the user changes at the destination Excel Workbooks (subscribers) can be destroyed daily, it would be a more simple publisher/subscriber model. Your time for maintenance would be regained over time.

So, give us more details on your Business specifications.

You could do this in Excel, but it would probably be more complex and the maintenance would most likely be many times higher.

 

[awade]

Thank you for your response Rx.
The master workbook is updated daily by data from another excel workbook that I don't have access to.
I then have to cut and paste each row with the corresponding serial number into its own workbook. (The 58 other workbooks) eg Eng SN 123 corresponds to workbook CAE540123 (the first 6 characters are the same for all engines)
The data is added to the data that is already in those workbooks (can't be over written) so that the data that is contained in these 58 other workbooks is then graphed and displayed in a database (access 2013)
If it can be made into a database table that is linked to the master workbook, and can add data to to each table without overwriting what was already there and allow for the outputs to be graphed that would be fantastic.

 

[Rx_]

OK, good information. So you are actually filtering one column then Appending those filtered rows to one of the existing 58 workbooks

I just had a big task come in, so anyone else please feel free to jump in.

Is the Master Workbook the same name after it is updated daily in the same file location?

Basically: Take MS Access and Link to Excel (the master)
Access can use this linked table (to Excel) like a regular table.
On the menu go to External Data. Use the Link option.
(another version might be to Import the daily import then append it to a local Access Table with a time stamp)

Test this out for one, then it will basically be the same steps:
A. Just 1 of the 58 workbooks and Link to it the same way (Subscriber Eng SN 123).
B. Write a Query for the Eng SN 123 in Master. Test it with the Select statement.
C. Turn the Select query into an Append query. - The destination is Subscriber Eng SN 123

If this works for one, you will have 58 Links (to workbooks a.k.a. Subscribers) to Excel Workbooks
You will have 58 Queries with filters (one made for each workbook subscriber).
Then we can help you create a dumb macro for Executing the SQL statements (58 of them) in one button.

Once we see examples, there might be a loop, but basically this is the process that can be improved on.

What version of MS Access are you going to use?

 

[awade]

Yes the master workbook keeps the same name.
I'm using Access 2013
Can access build effective graphs for the transferred data?

 

[awade]

I have made the table with the link to the master workbook.

Since i am very new to access, could you please expand on the query, select statement, select query and append query.

Thank you very much

 

[Rx_]

http://www.access-programmers.co.uk/microsoft-access-tutorials/
This web site has some great tutorials. Try this tutorial first, it is probably better than me trying to write it.

Be sure to do the same table link to one of your Excel subscribers.
Once you have watched the video, and linked both the master and subscriber:

The questions about the Select Query - turned into an Append query should be posted in the Access forum section Queries.
http://www.access-programmers.co.uk/forums/forumdisplay.php?f=8
It will become a focused question and there will be plenty of people there to help should I get too busy.
If you can take a screen shot of your Master Excel (as a linked table) and your single Subscriber Excel (as a linked table) will be straight-forward to the Query section. The fact it is Excel being used as a table shouldn't matter from this point of view.
For the Query, you will need to supply the string (value) or part of the string with a wildcard. e.g. Like 101* (items in this column that start with 101 and are followed by anyting else).

Once the Select Query returns the rows that are to be Appended to the Subscriber, it can be converted into an Append Query.
Once it works, you can save the name of the Append Query as a recgonized name to be reused for that one Subscriber Excel location.
- Then you will repeat this for each subscriber.
Regardless that the tables are really Excel, the Query group will be able to give you step-by-step instructions for an Append Query.

Once you have one working, return here or in the Access VBA section to ask how you can better automate the entire process. Lets put an end to all of the manual process.