I am developing a patch for an Excel macro driven Workbook application. Running this patch will change (in the main application) some form layouts, some formulas in cell and so on. But how do I instruct VBA to make changes to another programs VBA code?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Coding a Patch File
- Thread starter OnSite
- Start date
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
Here's an example I gleaned fro John Walkenbach's book. It replaces a VBA module with a different VBA module:
Code:
Sub UpdateUserBook()
Filename = "UserBook.xls"
' Activate workboook
On Error Resume Next
Workbooks(Filename).Activate
If Err <> 0 Then
MsgBox Filename & " must be open!", vbCritical
Exit Sub
End If
Msg = "This macro will replace Module1 in UserBook.XLS "
Msg = Msg & "with an updated Module." & vbCrLf & vbCrLf
Msg = Msg & "Click OK to continue."
If MsgBox(Msg, vbInformation + vbOKCancel) = vbOK Then
Call ReplaceModule
Else
MsgBox "Module not replaced!", vbCritical
End If
End Sub
Sub ReplaceModule()
' Export Module1 from this workbook
Filename = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("Module1") _
.Export Filename
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("Module1")
.Import Filename
End With
' Delete the temorary module file
Kill Filename
MsgBox "The module has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. The module may not have been replaced.", _
vbCritical
End Sub
Upvote
0
Wil Moosa
Well-known Member
- Joined
- Aug 11, 2002
- Messages
- 893
I used the above code and it worked fine.
I am curious
if is possible to "move" a userform into a workbook... and how the code would look like then?
Also, is there any code possible to remove/delete a userform in a workbook and to replace a userform in a workbook?
I am curious
if is possible to "move" a userform into a workbook... and how the code would look like then?Also, is there any code possible to remove/delete a userform in a workbook and to replace a userform in a workbook?
Upvote
0
Hi Wil,
All of what you asked for can be done. To import a userform you could do something like this:-
The userform1.frm file would have to exist already.
To delete an existing userform you could use something like this:-
You can also add a userform on the fly using something like:-
This code will add a userform to the active workbook. You can also add controls and code to the form 'on-the-fly'. There are some examples on the board e.g. http://www.mrexcel.com/board2/viewtopic.php?t=69137
All of what you asked for can be done. To import a userform you could do something like this:-
Code:
ActiveWorkbook.VBProject.VBComponents.Import "C:\temp\userform1.frm"The userform1.frm file would have to exist already.
To delete an existing userform you could use something like this:-
Code:
With ActiveWorkbook.VBProject
.VBComponents.Remove .VBComponents("Userform1")
End WithYou can also add a userform on the fly using something like:-
Code:
Set fm = ActiveWorkbook.VBProject.VBComponents.Add(3)This code will add a userform to the active workbook. You can also add controls and code to the form 'on-the-fly'. There are some examples on the board e.g. http://www.mrexcel.com/board2/viewtopic.php?t=69137
Upvote
0
OK, I should have mentioned this is my earlier post. When you export a form using Export File it creates two files - one called Userform1.frm and another called Userform1.frx. Both files need to be in the same folder in order for this to work. If you open the frm file in a text editor (e.g. Notepad) this is what the first few lines look like:-
You can see the reference to OleObjectBlob. My guess is that your folder only contains the frm file.
Code:
VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} frmOrganise
Caption = "Organise Favourites"
ClientHeight = 3525
ClientLeft = 45
ClientTop = 435
ClientWidth = 6165
OleObjectBlob = "frmOrganise.frx":0000
StartUpPosition = 1 'CenterOwner
EndYou can see the reference to OleObjectBlob. My guess is that your folder only contains the frm file.
Upvote
0
Wil Moosa
Well-known Member
- Joined
- Aug 11, 2002
- Messages
- 893
Dan, I removed the question from this tread before you answered but still appreciate your answer. I got around the problem but did not know why. I understand now.
Is it also possible to migrate one userform from one workbook in another (not only importing but actually first exporting, than importing but all this without leaving behind traces of Userform1.frm and Userform1.frx in the folder?
Is it also possible to migrate one userform from one workbook in another (not only importing but actually first exporting, than importing but all this without leaving behind traces of Userform1.frm and Userform1.frx in the folder?
Upvote
0
Here's an example of doing that. It just uses the same methods described above but uses the 'Kill' method to delete the files afterwards.
Code:
Sub ImportForm()
Dim wbSource As Workbook, wbDestination As Workbook
Set wbSource = Workbooks("Book1")
Set wbDestination = Workbooks("Book2")
wbSource.VBProject.VBComponents("Userform1").Export "C:\temp\Userform1.frm"
wbDestination.VBProject.VBComponents.Import "C:\temp\userform1.frm"
Kill "C:\temp\userform1.frm"
Kill "C:\temp\userform1.frx"
End Sub
Upvote
0
Similar threads
- Question
- Solved
