Bulk amendment to Macros?

[GarryBrogden]

Hi. I have an Access database that has a few hundred macros and VB modules that individually e-mail clients of ours with data in Excel or RTF format that is extracted from the database.

The problem is that my organisation has recently changed its name. That means that effectively most of the macros are out of date as in the 'send object' macro action, the e-mail 'Subject' is (like) 'ABC Co. Data Notification' and the 'Message Text' field now contains an old e-mail address for replies (i.e. 'don't reply to the e-mail that sent you this, any queries please reply to the e-mail enquiry address enquiries@abc.co').

So what I need to do is change all those 'Subject' fields to 'XYZ Co. Data Notification' and all the 'Message Text' fields to the revised e-mail address.

Probably a long shot and I suspect I know the answer (!) but would there be any automated way of doing this, or is it down to simply going through each individual macro and cutting and pasting? Thanks.

 

[namliam]

Not that I know...

But hopefully a good lesson for you to allways use VBA and not use Macro's
As well as DONT hardcode email addresses... Always stick them in some table someplace to use in code.
Much easier to mainain when needed.

 

[georgedwilkinson]

One thing I'd like to add to Mailman's suggestion is you shouldn't really hardcode anything (i.e. company name). Almost everything should be table driven.

From a VBA perspective, you can just go to the edit menu and replace all instances of a string in your entire (VBA) project. Good luck with the Macros thing cause I never (and never will) use them.

 

[datAdrenaline]

I am in 100% agreement with the previous posters with respect to there advice on what you *should* do with your code ....

With that, I want to point out that it is possible to replace text in a macro with new text in order to compensate for your situation of hard coded values in the macros.

I have used this code to replace text patterns like field names and table names and such, but it will work for your situation as well ... but I contend that if you understand this code, then you can create a more acceptable means to code your macros!! ... {note ... I am not saying that with arrogance/eliteness or any kind of tone like that ... so please do not mis-interpret that statement!! } ... I would consider the code below to be slightly advanced, thus my statement ... I hope that makes sense... So ... heres the code, and I do hate to post a "band aid" that may prevent you from implementing a more appropriate method to accomplish your task, but I also understand your delima, and I had the code in a module .... so .... learn from it, maybe use it, but you really need to consider the advice of the Mailman and George so you don't have to use this "patch" when something like this occurs. Please note that this code is un-forgiving, meaning that there is not confirmation messages. Also, its just sheer text replacement, so if you ask to replace "ABC" with "XYZ" it will replace ALL occurances of "ABC" with "XYX" even if the pattern is within a word.

Public Sub ModMacros(strOldText As String, strNewText As String)
'Replace text in a macro
 
    Dim obj As AccessObject
    Dim intChannel(1 To 2) As Integer
    Dim aStrMacros() As String
    Dim strFilename As String
    Dim strText As String
    Dim x As Long
 
    Const conFolder = "C:\Temp\"
 
    'Gather all the macro names into an array
    For Each obj In CurrentProject.AllMacros
        ReDim Preserve aStrMacros(x)
        aStrMacros(UBound(aStrMacros)) = obj.Name
        x = x + 1
    Next obj
 
    'Loop all the macro names, export to text, read & write files, import
    'the new file into a macro
    For x = LBound(aStrMacros) To UBound(aStrMacros)
 
        'Export the macro to text format
        strFilename = aStrMacros(x) & ".mcr"
        SaveAsText acMacro, aStrMacros(x), conFolder & "~tmp_" & strFilename
 
        'Open the text file, and create a new file to write into
        intChannel(1) = FreeFile
        Open conFolder & "~tmp_" & strFilename For Input Access Read As #intChannel(1)
 
        intChannel(2) = FreeFile
        Open conFolder & strFilename For Output Access Write As #intChannel(2)
 
        'Dump one file into the other, while changing the text as appropriate
        Do Until EOF(intChannel(1))
 
            'Read in a line from the created macro file
            Line Input #intChannel(1), strText
 
            'Write the line to the new macro file, whilst
            'replaceing the new with the old
            strText = Replace(strText, strOldText _
                                     , strNewText _
                                     , 1, -1, vbTextCompare)
 
            Print #intChannel(2), strText
 
        Loop
 
        'Close the files
        Close #intChannel(1)
        Close #intChannel(2)
 
        'Import the NEW macro file
        LoadFromText acMacro, aStrMacros(x), conFolder & strFilename
 
        'Clean up the text files
        Kill conFolder & "~tmp_" & strFilename
        Kill conFolder & strFilename
 
    Next x
 
End Sub

 

[GarryBrogden]

Thank you all, especially datAdrenaline.

I personally am conversant with VBA, but this database was designed to offer a quick and easy solution for non-IT literate staff to make on the fly amendments and additions. Not that that's any excuse, but that's the reason it grew like topsy!

 

[datAdrenaline]

You are most welcome Garry! ... Also, I am all too familiar with the "quick and easy" solutions for non-it folk ... I think I spend half my time on them!!!! .... Good Luck with your project!!! ...