Newbie question, XL2003, VBA deleting/adding modules in multiple workbooks

[KR]

I have hundreds of end-user workbooks that all have the same module on our
network. I just found out that there is an error in the module, and I've
created an updated module that fixes the problem and I need to replace it in
all of the workbooks without messing with the data or settings of those
workbooks.I've exported a copy of the fixed module onto our network so my
code can pull it into each of the other workbooks.

Based on code from Chip Pearson's site, I've grabbed what seems to be the
key lines to add to my code that loops through and finds all of the affected
workbooks. However, I haven't any experience working with VBA at this level,
and I'm not sure which part to tweak to grab an exported module instead of
creating one from scratch.

From Chip's site:

'this part will hopefully remove the old version of the module
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("ValidateAndPasteData")
ThisWorkbook.VBProject.VBComponents.Remove VBComp

'How do I modify this to import my ValidateAndPasteData.bas file?
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "NewModule"

'I'm thinking something like:
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(mypath &
"ValidateAndPasteData.bas")
'VBComp.Name = "NewModule"

but since this is a live environment and I don't have a good way to test
this before running it, I'm a little anxious to just do my usual
trial-and-error, because if the delete code works and the add code doesn't,
then I'll have broken all of the workbooks and angry coworkers...

Thanks for any help,
Keith

 

[Tom Ogilvy]

Farther down on the page it shows you how to copy modules between workbooks.
On part of the code show an example of importing a .BAS file.

from the code:



Dim FName As String

. . .

fname = mypath & "ValidateAndPasteData.bas"
Workbooks("ExistingBook.xls").VBProject.VBComponents.Import FName

You definitely need to make a **copy** of a few of the workbooks and put
them in an isolated location and test your code.

 

[KR]

Tom- thank you for your assistance (and patience) - I now have the second
half of this working based on your help...but I took your advice to create a
test area with copies of these files, and it turns out the first part of my
code doesn't work- the part that removes the old/broken version of the
module.

Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0, False)

Dim VBComp As VBComponent
Set VBComp =
mybook.VBProject.VBComponents("ValidateAndPasteData")
mybook.VBProject.VBComponents.Remove VBComp

'this part works:
Dim FName As String
FName = MyPath & "ValidateAndPasteData.bas"
mybook.VBProject.VBComponents.Import FName

mybook.Close savechanges:=True


It doesn't error out or anything, it just happily processes all my
workbooks. But when I go back in to the VBE, I see the original module (with
the bad code) is still there, and the imported module has been appended with
a number, e.g. I still have a module called ValidateAndPasteData, and now I
have one called ValidateAndPasteData1 (then ValidateAndPasteData2, etc for
as many times as I run the code). Those new modules do have the updated
code. They are regular modules, I've never used "class" modules.

Many thanks,
Keith

 

[Tom Ogilvy]

Are you removing and importing in the same macro. I would try doing it
separately



First Macro:
Loop through the workbooks
for each workbook
remove
save
close
next
End of the first macro

' before starting the second macro, check a few of the workbooks and make
sure the module has been remove.d

Second macro
Loop through the workbooks
for each workbook
import
save
close
End of the second macro

 

[KR]

Yep, I was trying to do it in the same macro, to minimize the downtime of
each file. I'll split it out, and test it again.
Thanks again for the advice,
Keith

 

[Tom Ogilvy]

If the removing part doesn't work, then I think you will need to break it
into two pieces.

Put a list of files in a worksheet in the workbook with the code

first macro finds the first entry in the column
if no names found, quit
opens that file
removes the module
Application.Ontime now(),"SecondMacro"
End

Second Macro
finds the first entry in the column
strips off the path and uses the name to Save and close the workbook
clears that entry
Application.OnTime now(),"FirstMacro"
End Sub

I have found when working within a workbook and deleting a module using code
within that workbook, the module doesn't get deleted until the macro stops.
I am surprised if this is the case for an external macro, but in case you are
having problems, you can try the above. the use of application.Ontime allows
the macro to end.

 

[KR]

It worked, so I also tested an interim solution- I just saved the target
file between the removal of the old module and the import of the new module,
and that was enough to make it work as desired. I'm not sure what the saving
does- maybe forces Excel to process the removal command? But no matter, it
works!

Thanks again,
Keith