isladogs
MVP / VIP
- Local time
- Today, 13:51
- Joined
- Jan 14, 2017
- Messages
- 18,822
Editing macro design as part of a full database search using VBA
Hi
I have recently completed a full database SQL search project for selected text in all database items: query SQL / table fields / module code (standard / class / form & report) & macros. All of this is working fine.
Now I'm in the process of extending this to do find & replace in all the above items. For the sake of completeness, I'm currently trying to do this with macros which are causing some difficulties!
You can't edit macro design using VBA but you can export the design
So, the approach is
a) export the macro as a text file
b) replace selected text in the text file
c) reimport the text file overwriting the original macro
d) delete the text file
The code fails with error 2128 at step c) - import
This is because step b) completely alters the file contents
See attached screengrabs - original & modified text file
The code below is my latest attempt
Manual editing of the text file works fine but I need to do this using VBA as it forms part of looping through all database objects
If anyone has any experience in doing something similar & can advise, I would be very grateful
For info, the author of the excellent V-Tools addin was also unable to solve this though he used a very different approach
Hi
I have recently completed a full database SQL search project for selected text in all database items: query SQL / table fields / module code (standard / class / form & report) & macros. All of this is working fine.
Now I'm in the process of extending this to do find & replace in all the above items. For the sake of completeness, I'm currently trying to do this with macros which are causing some difficulties!
You can't edit macro design using VBA but you can export the design
So, the approach is
a) export the macro as a text file
b) replace selected text in the text file
c) reimport the text file overwriting the original macro
d) delete the text file
The code fails with error 2128 at step c) - import
This is because step b) completely alters the file contents
See attached screengrabs - original & modified text file
The code below is my latest attempt
Code:
Public Function ModifyMacroVBA(strMacroName, strFind, strReplace)
On Error GoTo Err_Handler
'Const ForReading = 1
'Const ForWriting = 2
Dim objFSO As Object
Dim objFile As Object
'Dim strOldText As String
'Dim strNewText As String
' Dim strBuf As String
Dim strTemp As String
' Dim iFileNum As Integer
Dim strFileName As String
' Dim filesys As Variant
Dim tempfile As Object
Dim file As Object
Dim CurrentLine As String
Dim NewLine As String
'Dim strMacroName As String
'1. save macro to text file in same folder
Path = Application.CurrentProject.Path
strFileName = Path & "\" & strMacroName & ".txt"
' Debug.Print strMacroName, strFilename
' Application.SaveAsText acMacro, strMacroName, strFileName
'Exit Function
'2. modify the text file
'================================
'THIS DOESN'T WORK for macros-'corrupts' the text
'create a temp file then output the edited file contents
strTemp = Path & "\" & strMacroName & ".tmp"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(strTemp, True)
'open the original file and for each line replace any matching text
Set file = objFSO.OpenTextFile(strFileName)
Do Until file.AtEndOfStream
CurrentLine = file.ReadLine
NewLine = Replace(CurrentLine, strFind, strReplace, , , vbTextCompare)
'write to the new line containing replacements to the temp file
objFile.WriteLine NewLine
Loop
file.Close
objFile.Close
'delete the original file and replace with the temporary file
objFSO.DeleteFile strFileName, True
objFSO.MoveFile strTemp, strFileName
'===================
Previous attempt - also failed
' Set objFSO = CreateObject("Scripting.FileSystemObject")
' Set objFile = objFSO.OpenTextFile(strFilename, ForReading)
' strOldText = objFile.ReadAll
' objFile.Close
' strNewText = Replace(strOldText, strFind, strReplace)
'' Debug.Print strNewText
' Set objFile = objFSO.OpenTextFile(strFilename, ForWriting)
' objFile.WriteLine strNewText
' objFile.Close
'===================
'3. import the text file overwriting the existing macro
Application.LoadFromText acMacro, strMacroName, strFileName
' Application.LoadFromText acMacro, strMacroName, strTemp
'4. delete the text file
Kill strFileName
Exit_Handler:
Exit Function
Err_Handler:
If Err <> 2128 Then
'create error message & log
FormattedMsgBox "Error " & Err.Number & " in ModifyMacroVBA procedure : " & _
"@" & Err.Description & " @", vbCritical, "Error modifying macro"
' strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
' PopulateErrorLog
End If
Resume Exit_Handler
End Function
Public Function TestModifyMacroVBA()
ModifyMacroVBA "mcrOpenMainMenuBKP", "MainMenu", "AdminMenu"
End FunctionManual editing of the text file works fine but I need to do this using VBA as it forms part of looping through all database objects
If anyone has any experience in doing something similar & can advise, I would be very grateful
For info, the author of the excellent V-Tools addin was also unable to solve this though he used a very different approach
Attachments
Last edited:
