Multiple Excel Spreadsheets sharing VB & Auto Refresh for Imported Data

[MSAccessRookie]

One of the requirements of the project that I am presently working on (MS Office 2003 Format), is to write VB Code to validate the contents of Excel Cells as they are being filled in. To fulfill this requirement, the contents of an MS Access Table are imported into a Worksheet and then used as a filter in a dropdown menu.

• Problem #1

While the Dropdown Menu works as expected (with limited issues that still need to be dealt with), It turns out that when the MS Access Database Table is updated, the data in the Excel Worksheet does not get refreshed unless we do it manually.

I tried to use the Excel Settings for Auto Refresh (Data->Import External Data-->Data Range Properties-->Refresh Data on File Open), but this created a warning message each time that the Spreadsheet was opened that was not acceptable to the users.
​

• Problem #2

The purpose of the Excel Spreadsheet is to contain the Formulas for creating the products that the company sells. Production Users will enter information such as Compound Names, Lot Numbers, and amounts (weights) of each compound that is to be used. The Spreadsheet has the appropriate formulas to do the proper checking, and the new VB Code will verify that the Lot Number contains the appropriate compound.

The same VB Code is able to work for all of the Spreadsheets, since they all have the same relative structure (each row is formatted the same way, although not all of the Spreadsheets have the same number of rows). This will result in numerous Spreadsheets that all contain the same VB Code (over 20 so far, and over 500 expected by the end of the project).

Is there any way for all of the Spreadsheets to share the VB Code?
​

 

[chergh]

It turns out that when the MS Access Database Table is updated, the data in the Excel Worksheet does not get refreshed unless we do it manually.

I tried to use the Excel Settings for Auto Refresh (Data->Import External Data-->Data Range Properties-->Refresh Data on File Open), but this created a warning message each time that the Spreadsheet was opened that was not acceptable to the users.

I don't see a warning message being an issue as you can train staff what to do with it as it will be the same message each time, they will have to deal with the macro warning message anyway so I don't see why one more should be an issue. I would usually push back to whoever said it wasn't acceptable but you might not be in a situation to do that. You should be able to add code to the open event of the worksheet to grab the data you want or add code to the get focus event of the combo box to get the data for it.

Is there any way for all of the Spreadsheets to share the VB Code?
[/INDENT]

Creating an addin from your code would seem to be the easiest way to achieve this though I am guessing you are just wanting to update the code in one place and each time you realeased a new version of the addin it would probably need to be installed on each machine, I think not 100% certain on this. I don't use addins a whole lot so it may be possible to just update a central xla file and have the functionality available without having to reinstall the addin.

 

[MSAccessRookie]

I appear to have found a Work-Around (Ultimate Fix?) for Problem #1.

I call it a Work Around, because it is limited to Spreadsheets where the External Data is being stored in WorkSheet #1, and the desired first WorkSheet to Display is WorkSheet #2. I am aware that it can be easily modified to refer to other sheets, but I am striving for a User Oriented Solution. Any advice on improving the solution to make it more flexible?

[FONT=Courier New]Private Sub Workbook_Open()[/FONT]
 
[FONT=Courier New]  Sheet1.Activate                 ' Ingredient Table Sheet[/FONT]
 
[FONT=Courier New]  Columns("A:A").Select           ' Refresh the Ingredients[/FONT]
[FONT=Courier New]  Selection.QueryTable.Refresh    ' Table located in Column A[/FONT]
 
[FONT=Courier New]  Sheet2.Activate[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

I am still Searching for a Solution to Problem #2. I tried to determine how to create an appropriate Add-In, and was not successful. Additional advice in that area would also be appreciated.

 

[vbaInet]

For #2 I would suggest creating an excel template, everytime you want to use it, open it and Save As. Then append data onto the newly created spreadsheet. That way you don't have to worry about transferring code over.

 

[MSAccessRookie]

For #2 I would suggest creating an excel template, everytime you want to use it, open it and Save As. Then append data onto the newly created spreadsheet. That way you don't have to worry about transferring code over.

Thank you for the reply, but if you mean what I think that you mean, then I am already doing that. We start by copying an existing Spreadsheet and using that as the Template that you described, and then we make any necessary changes to create the new formula.

My apologies for any confusion, but my issue is regarding changes that are made after a form has been created. During the creation of the first 12 forms, I discovered 3 separate reasons to change the VB Code. Since all of the Spreadsheets use the same VB Code, I was hoping to find a way to share the code so that it would only need to be updated in one place in the event that further changes are required.

 

[chergh]

I would suggest asking this on Mr Excel forums there are probably people there who have done what you are trying to do rather than me or someone else here trying to figure it out as we go along. Just remember to post a link to here and let us know if you get an answer.

 

[MSAccessRookie]

I would suggest asking this on Mr Excel forums there are probably people there who have done what you are trying to do rather than me or someone else here trying to figure it out as we go along. Just remember to post a link to here and let us know if you get an answer.

I have tried doing that, and believe it or not, I have still gotten the best ideas from the people here at AWF. Any ideas as to any additional Excel Forums that I could try would be appreciated, and I am willing to try any that I have not already. I will post back here when I make any final resolution to the issues, unless the resolution comes from here.

 

[vbaInet]

I can only think of two ways:

1. Use a module and refer all events to the module.

OR

2. Make the change in one spreadsheet module and copy the contents into the others

 

[MSAccessRookie]

I can only think of two ways:

1. Use a module and refer all events to the module.

OR

2. Make the change in one spreadsheet module and copy the contents into the others

I am not sure what you meant by Option #1, but for all intent and purposes, Option #2 is what I am doing at this time.

When we discover a change needs to be made, we make and test the change in the Spreadsheet where the neeed for change was discovered, and then propogate it out on the others after the change is completed. As long as the number of Spreadsheets remains under 50, this could remain a possibility, but because the number of distinct Spreadsheets could exceed 3000 in total (300 within 6 months or less), you can see how this would not be an option In the long run. That is why I am looking for alternatives.

 

[chergh]

I don't suppose all the spreadsheets are conveniently located in 1 or 2 folders?

 

[vbaInet]

I think we need to get a clear picture here, whether you mean Workbooks or Spreadsheets. So when you say 50 + Spreadsheets, do you mean Spreadsheets in the same Workbook or Spreadsheets in separate Workbooks?

If we're talking about 50+ Spreadsheets in the same Workbook then my thinking was Option #1. But thinking about the number of Spreadsheets I doubt you have them all in one Workbook. If they're not on the same workbook then there's no way of redirecting events to another document or object.

So now I would imagine you are talking about separate workbooks and the following is a possible way of updating all spreadsheets. If they are located in one folder (like chergh enquired), you could read off the code from a Text file on Open and save it into the workbook. You would only do it if On Open the Last Updated Date on the Workbook (comment header) is less than the Last Updated Date on the Text file header. We're talking about working with the VBA Editor now.

 

[MSAccessRookie]

I don't suppose all the spreadsheets are conveniently located in 1 or 2 folders?

Currently there are over 3000 Spreadsheets (each with 2 or more WorkSheets) that are all located in the same directory. A typical Production Supervisor does one of the following on a regular basis:

1. Make a copy of an existing Formula Template with a specific naming convention which includes the Batch Number and Date.
2. Make a copy of an existing Formula Template and make changes to it, creating a New Formula Template, and then make a copy of the New Formula Template with a specific naming convention which includes the Batch Number and Date.

A copy of the Spreadsheet that is created is given to the Production Users for the purpose of creating a Batch of an New product for sale to customers.

 

[MSAccessRookie]

I think we need to get a clear picture here, whether you mean Workbooks or Spreadsheets. So when you say 50 + Spreadsheets, do you mean Spreadsheets in the same Workbook or Spreadsheets in separate Workbooks?

If we're talking about 50+ Spreadsheets in the same Workbook then my thinking was Option #1. But thinking about the number of Spreadsheets I doubt you have them all in one Workbook. If they're not on the same workbook then there's no way of redirecting events to another document or object.

So now I would imagine you are talking about separate workbooks and the following is a possible way of updating all spreadsheets. If they are located in one folder (like chergh enquired), you could read off the code from a Text file on Open and save it into the workbook. You would only do it if On Open the Last Updated Date on the Workbook (comment header) is less than the Last Updated Date on the Text file header. We're talking about working with the VBA Editor now.

My understanding of the layout for Excel is that a Spreadsheet refers to a file on your disk, while a Worksheet refers to an individual page of any Worksheet. Currently my Archive contains over 3000 Spreadsheets (each with 2 or more Worksheets) that are all located in the same directory.

Reading the Code from an external file and updating it is an interesting concept that I will need to explore this in more detail.

 

[vbaInet]

Different terminology. I tend to call Spreadsheets Workbooks

Yes, I think that would be your best option. You could even create a function to :

1. Check whether the file is in use
2. Read the header information of the file to check the date it was last updated. Header being you could create a comment at a specific line in the module with this information
3. Update if necessary

There's a link from CPearson regarding coding the VBE editor.
3.