Solved: Change Code Modules and Userforms

[bryVA]

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?

[VBA]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
[/VBA]


Thanks all for your help,

- B

[Benzadeus]

[VBA]Sub ImportCodeModule()
Dim Filt$, Title$, Filename$, Message As VbMsgBoxResult

Dim intExtension As Integer
Dim intLastBar As Integer
Dim n As Integer
Dim strFile

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

'This extract filename without extension and \, eg: C:\felipe\test.bas becomes test
For n = Len(Filename) To 1 Step -1
If Mid(Filename, n, 1) = "." Then
intExtension = n
End If
If Mid(Filename, n, 1) = "\" Then
intLastBar = n
Exit For
End If
Next n
strFile = Mid(Filename, intLastBar + 1, intExtension - intLastBar - 1)

'look for a module with the same name you imported and delete it if finds
With Application.VBE.ActiveVBProject
For n = 1 To .VBComponents.Count
If .VBComponents(n).Name = strFile Then
.VBComponents.Remove .VBComponents(n)
End If
Next n
End With

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
[/VBA]

[bryVA]

Thanks Benzadeus. That helps a great deal.

One last issue. There is a VBA password protecting the code so could the macro containing the password that automatically unlocks the code then runs the macro that you wrote for me and then lock it back up? Lets say the password is letmein how would I create that macro?

Thanks again for yout help,

-B

[Benzadeus]

I don't know how to unlock VBE via macro, unless using SendKeys method, as described here: http://groups.google.com/group/micro...26sa%3DN&pli=1

Maybe some guru here know a different answer... why not mark this thread as solved and then open a new one with the question?

[bryVA]

Thank you for your help. I found the code to do send keys but it doesn't look like it is reliable so I will just have to have the code unlocked to do this.

Again thanks a lot. I appreciate that very much.

-B