bryVA
07-30-2009, 12:43 PM
Hello all,
I have a program that has code and information on the sheets that different locations use have have different information in the sheets. When I update the code to add functionality to excel and distribute it out to the other locations they then need to adjust what is on the sheets again.
To get around this issue I found this code that will allow the users to click on a command button to run this code and have it import the code. However I need it to first delete the old module that is named the same and then import the new module or form. I also need it to unlock the VBA Window so it can delete then import the needed module or form.
I was wondering if I could have it select a folder and it will unlock the VBA window, delete the modules in the file that have updated modules in the selected folder and then upload all the modules and forms in the folder so that it is a two step update process. They click the button, select the folder that only the new code is stored in and the macro will do the rest by deleting the modules and replacing them.
This is the simple code I have to add the module. How would I do the rest of it?
Sub ImportCodeModule()
Dim Filt$, Title$, Filename$, Message As VbMsgBoxResult
Do
Filt = "VB Files (*.bas; *.frm; *.cls)(*.bas; *.frm; *.cls),*.bas;*.frm;*.cls"
Title = "SELECT A FOLDER - CLICK OPEN TO IMPORT - CANCEL TO QUIT"
Filename = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=5, Title:=Title)
On Error GoTo Finish
Application.VBE.ActiveVBProject.VBComponents.Import (Filename)
Message = MsgBox(Filename & vbCrLf & " has been imported - more imports?", vbYesNo, "More Imports?")
Loop Until Message = vbNo
Finish:
Message = vbYes
End Sub
Thanks all for your help,
- B
I have a program that has code and information on the sheets that different locations use have have different information in the sheets. When I update the code to add functionality to excel and distribute it out to the other locations they then need to adjust what is on the sheets again.
To get around this issue I found this code that will allow the users to click on a command button to run this code and have it import the code. However I need it to first delete the old module that is named the same and then import the new module or form. I also need it to unlock the VBA Window so it can delete then import the needed module or form.
I was wondering if I could have it select a folder and it will unlock the VBA window, delete the modules in the file that have updated modules in the selected folder and then upload all the modules and forms in the folder so that it is a two step update process. They click the button, select the folder that only the new code is stored in and the macro will do the rest by deleting the modules and replacing them.
This is the simple code I have to add the module. How would I do the rest of it?
Sub ImportCodeModule()
Dim Filt$, Title$, Filename$, Message As VbMsgBoxResult
Do
Filt = "VB Files (*.bas; *.frm; *.cls)(*.bas; *.frm; *.cls),*.bas;*.frm;*.cls"
Title = "SELECT A FOLDER - CLICK OPEN TO IMPORT - CANCEL TO QUIT"
Filename = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=5, Title:=Title)
On Error GoTo Finish
Application.VBE.ActiveVBProject.VBComponents.Import (Filename)
Message = MsgBox(Filename & vbCrLf & " has been imported - more imports?", vbYesNo, "More Imports?")
Loop Until Message = vbNo
Finish:
Message = vbYes
End Sub
Thanks all for your help,
- B