Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question, XL2003, VBA deleting/adding modules in multiple workbooks
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("ValidateAndPa steData") ThisWorkbook.VBProject.VBComponents.Remove VBComp 'How do I modify this to import my ValidateAndPasteData.bas file? Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question, XL2003, VBA deleting/adding modules in multiple w
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.VBComponen ts.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. -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPa steData") ThisWorkbook.VBProject.VBComponents.Remove VBComp 'How do I modify this to import my ValidateAndPasteData.bas file? Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question, XL2003, VBA deleting/adding modules in multiple w
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("ValidateAndPasteDat a") 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" wrote in message ... 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.VBComponen ts.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. -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPa steData") ThisWorkbook.VBProject.VBComponents.Remove VBComp 'How do I modify this to import my ValidateAndPasteData.bas file? Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question, XL2003, VBA deleting/adding modules in multip
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 -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPasteDat a") 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" wrote in message ... 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.VBComponen ts.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. -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPa steData") ThisWorkbook.VBProject.VBComponents.Remove VBComp 'How do I modify this to import my ValidateAndPasteData.bas file? Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question, XL2003, VBA deleting/adding modules in multip
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" wrote in message ... 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 -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPasteDat a") 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" wrote in message ... 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.VBComponen ts.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. -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPa steData") ThisWorkbook.VBProject.VBComponents.Remove VBComp 'How do I modify this to import my ValidateAndPasteData.bas file? Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question, XL2003, VBA deleting/adding modules in multip
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. -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPasteDat a") 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" wrote in message ... 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.VBComponen ts.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. -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPa steData") ThisWorkbook.VBProject.VBComponents.Remove VBComp 'How do I modify this to import my ValidateAndPasteData.bas file? Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question, XL2003, VBA deleting/adding modules in multip
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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPasteDat a") 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" wrote in message ... 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.VBComponen ts.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. -- Regards, Tom Ogilvy "KR" wrote: 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("ValidateAndPa steData") ThisWorkbook.VBProject.VBComponents.Remove VBComp 'How do I modify this to import my ValidateAndPasteData.bas file? Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Question Column Adding Please Help | New Users to Excel | |||
Newbie question: Importing modules (.bas) at startup? | Excel Programming | |||
Newbie VBA Question; Deleting minimum value row | Excel Programming | |||
Newbie question on deleting duplicate rows | Excel Programming | |||
using forms and modules in multiple workbooks | Excel Programming |