PDA

View Full Version : Solved: Edit/update code in multiple workbooks



rushN
05-15-2010, 08:30 PM
Hi everyone

I have many thousands of Excel workbooks that contain multiple sheets with attached VBA code. The code is attached to a button and is designed to perform various formatting functions on the sheets within the workbook. When the code was originally written, Arial was the preferred font for the sheets, so this was hard coded into the VBA code to set the formatting of the sheet to that font (amongst other things).

Now, the preferred font has changed. I need some way of automating the updating of the code in these thousands of workbooks to replace the hard coded font. Each workbook contains multiple sheets and multiple VBA objects.

I would be happy to have my users add a new sheet to the workbook as it is next opened that contains another VBA object to edit the existing code then delete itself from the workbook, but I'm struggling to find a way of automating the editing of the exiting code.

Would anyone have any suggestions?

Cheers

GTO
05-15-2010, 09:42 PM
Where does the code reside that needs changed? You mention a button on a sheet, but it is unclear whether there is one button per workbook, or a button on each sheet in every workbook, etc.

More on point, is the code that the button(s) run residing in the sheet's module, or a standard module?

As you mentioned 'thousands' of workbooks, I am guessing that they are in the hands of a lot of users, and that you do not have access to all the workbooks. Is this the case, that is, whatever we do, we must get the user(s) to do for us?

I am also guessing that there are not thousands of markedly different wb's, as to the code residing therein. Could you zip an example wb of the current and an example of what the 'after' should be and attach the zip?

If you are using 2007 / .xlsm format, please use .xls format as some here (including yours truly if at home) cannot read the newer.

rushN
05-15-2010, 10:31 PM
Hi GTO

Thanks for your reply. More info...

...the code resides in the individual sheet, with each workbook having a number of sheets. Some sheets don't have code attached, and some do. The code is specific to the sheet to which it's attached and operates specifically on that sheet only (ie: not the entire workbook).

All the workbooks are located on our servers, so I have access to all of them. The workbooks are used in an accounting application, and the sheets form part of the financial statements produced by that program.

What we have is a number of "master workbooks" which contains all possible sheets that a user can add to their "client workbook". The users copy sheets from the masters to the client to build up the client workbook with the necessary reports. They may take one, several, or all of the "master" sheets, depending on their needs. Therefore, the code attached to a specific sheet in the client is the same across all client workbooks, but each master sheet has different code.

(I hope all that makes sense :dunno )

Given the workbooks contain confidential client information, I can't really post up an example. If this is going to mean the difference between finding a solution and not, then I'll spend the time to pull together an example with dummy data and upload.

Cheers

mikerickson
05-15-2010, 10:39 PM
You could create a new workbook, that contains the correct code.

It would also contain a routine that:

1)Deletes an existing vbComponent from the old workbook.
2)Exports the good vbComponent from the new workbook
3)Imports the good vbComponent to the old workbook.


Also, instead of hard coding the new preferred font, you could use Public constant, so that when a new preferred font comes along, only one line of code needs to be changed.

rushN
05-15-2010, 10:56 PM
Example workbook attached for those interested.

GTO
05-16-2010, 06:26 PM
...the code resides in the individual sheet, with each workbook having a number of sheets. Some sheets don't have code attached, and some do. The code is specific to the sheet to which it's attached and operates specifically on that sheet only (ie: not the entire workbook).

Okay, as I understand it, we are talking about individual sheets in one of a number of 'master' workbooks. Thus - we are looking to replace code not only in the sheet modules of these several workbooks, but the copied sheets in a plethora of user-built workbooks.


All the workbooks are located on our servers, so I have access to all of them.

At least to me, this seems great news! I have never attempted what you are looking to do, but I'd rather stick a needle in my eye than explain to( what I assume are) a notable number of users, to tick the Trust Access checkbox.


What we have is a number of "master workbooks" which contains all possible sheets that a user can add to their "client workbook". The users copy sheets from the masters to the client to build up the client workbook with the necessary reports. They may take one, several, or all of the "master" sheets, depending on their needs. Therefore, the code attached to a specific sheet in the client is the same across all client workbooks, but each master sheet has different code.

I think/hope I have a basic picture... Again, someone may have a stronger sense of this or better yet, experience with a similar circumstance. That said, here are the issues I see:

Normally, I would be thinking about replacing code modules, like Mike suggested. But here we have sheet modules, so each module's name is the sheet's codename. Thus - if there is a (codename) Sheet33 already in the destination wb, then if a sheet containing code is copied from a 'master' wb to the user's current wb and this copied sheet happens to have a codename of Sheet33, the codename/module name is being changed along the way.
Now we could look in the first few lines of each module and see if we find: "If ActiveWorkbook.Name <> "BCA-Masters - Assets & Liabilities.xls" Then" for instance; but then if we are already needing to search the modules, maybe replacing lines of code would be a decent idea in this case.
Please note that I am not saying that we would not want to verify that we are in one of the correct modules, as another concern that I would have, is that I would not wnat to be accidently modifying some wb that the user had put together.With the above in mind, try this 'baby-step'. Please note that it is not intended to be a solution, just a test. Given that upon running whatever solution is arrived at, you will have permanently changed 1000+ wb's, I'd strongly suggest good testing while developing.

Create a temporary folder.
Save the attached to it, along with several throwaway copies of randomly selected 'client' workbooks.Run the test and see if we get the desired results...

In a Standard Module:


Option Explicit

Sub Temp()
Dim _
FSO As Object, _
fsoFolder As Object, _
fsoFile As Object, _
REX As Object, _
target_VBProject As Object, _
target_vbComponent As Object, _
target_CodeModule As Object, _
wbTarget As Workbook, _
i As Long, _
LeadingSpaces As Long, _
strREXReturn As String

'// Set a reference and create a pattern to find anything that is NOT A-Z,a-z, //
'// a stop/dot, an equal sign, or a double-quote mark. Global is True, so that we //
'// replace all, and end up with a compressed string. Just one way, but I figured //
'// this gets rid of concerns for a false match and/or indentation/lead spaces. //
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.IgnoreCase = True
.Pattern = "[^a-z\.\=\""]*"
End With

'// In this case, we are just grabbing the folder that ThisWorkbook is in, but we //
'// may be able to use FSO to loop thru folders later. //
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = FSO.GetFolder(ThisWorkbook.Path & "\")

For Each fsoFile In fsoFolder.Files
'// Ensure we don't try and open ThisWorkbook, nor open any non excel files. //
If Not fsoFile.Name = ThisWorkbook.Name _
And fsoFile.Type = "Microsoft Excel Worksheet" Then

'// Set a reference to the opening wb. Not sure about links, a guess. //
Set wbTarget = Workbooks.Open(Filename:=fsoFile.Path, _
UpdateLinks:=False, _
ReadOnly:=False)

'// ...and a ref to the target wb's VB Project... //
Set target_VBProject = wbTarget.VBProject

For Each target_vbComponent In target_VBProject.VBComponents

Set target_CodeModule = target_vbComponent.CodeModule

'// Loop though the lines in each code module... //
For i = 1 To target_CodeModule.CountOfLines
'// ...seeing if we find an exact match of our 'compressed' string, //
If REX.Replace(target_CodeModule.Lines(i, 1), vbNullString) _
= ".Name=""Arial""" Then
'// ...and when we find a match, see how many leading spaces //
'// there are, so we can keep the same indentation. //
LeadingSpaces = InStr(1, target_CodeModule.Lines(i, 1), ".Name")
'// Then replace the line with the font now desired. //
target_CodeModule.ReplaceLine _
i, _
IIf(LeadingSpaces > 0, _
Space(LeadingSpaces - 1), _
vbNullString) & ".Name = ""Times New Roman"""
End If
Next
Next

If Not wbTarget.Saved Then
wbTarget.Close SaveChanges:=True
Else
wbTarget.Close
End If
End If
Next
End Sub

GTO
05-16-2010, 06:32 PM
One would think that I would remember to attach the wb after taking a while to figure it out, wouldn't one? Oh well... here:

rushN
05-16-2010, 08:56 PM
Wow! All I can say is GTO=Legend!

This works perfectly. I ran the code over a duplicated sample of our workbooks and it does everything I need. :thumb

I don't know what to say, other than thank you so much. You have resolved what I considered to be a very difficult task.

All the best

:beerchug:

Blade Hunter
05-16-2010, 10:54 PM
Can you look at an Excel addin to prevent you having to maintain multiple copies of the same code?

rushN
05-16-2010, 11:04 PM
I guess so. I wouldn't know where to begin though :dunno

Cheers

Blade Hunter
05-16-2010, 11:47 PM
Basically create a module in a workbook, put your code in their. save a COPY when you are done because a compiled XLA can't be opened for editing again.

Save a second copy as an Excel Add In

On the client machines add it as an add in (from the tools menu)

The sub routines in your add in are now available to you from within any workbook.

The beauty of this is you host the XLA on the network drive so when you make a code change you update your code, save a copy as an XLA and overwrite.

Voila, everyone is updated.

rushN
05-17-2010, 12:32 AM
Sweet! Thanks for that. I'll give it a whirl.

Cheers

GTO
05-17-2010, 04:47 AM
...This works perfectly. I ran the code over a duplicated sample of our workbooks and it does everything I need. :thumb

I don't know what to say, other than thank you so much. You have resolved what I considered to be a very difficult task...

You are of course most welcome. Unless you are moving the files to another folder, running, and returning the files, OR, are sure that the users do not have any of their own files in the folders, I would still look at more "safetys" to ensure I did not overwrite a user's project.

Of course you are looking at the folders/files and may have already R/O any issues - in which case I am certainly gladd it helped:)

Mark

rushN
05-17-2010, 05:02 AM
You are of course most welcome. Unless you are moving the files to another folder, running, and returning the files, OR, are sure that the users do not have any of their own files in the folders, I would still look at more "safetys" to ensure I did not overwrite a user's project.

Of course you are looking at the folders/files and may have already R/O any issues - in which case I am certainly gladd it helped:)

Mark

Hi Mark

Again, thank you so much.

I tried sending you a PM, but I couldn't, likely due to my low post count. Could you please PM me an email address so I can send you a private message?

Many thanks

GTO
05-17-2010, 10:12 AM
I rarely check email and the option to send email through the site seems to have disappeared.

Try PM'ing again. I recall being very successful at PMing when I first joined.