macro to backup my VBA/macro text

Dr. Rick · #1 02-07-2025, 06:54 AM

Since Word keeps arbitrarily and apparently randomly losing my macros, I am trying to record a macro that will, after going to "edit" in the list macros window, highlight all the text, copy it, exit and paste and save this to a blank document as a "backup."

When I'm in a document and go to the macro list window, the edit choice has a line under the "E", which makes selecting it easy (I can either click on it, or press Alt-E) When I try to do this from the environment of "record a macro", the underline under the "E" is significantly, GONE and prevents me from getting into and saving the macros.

I will happily accept charity (i.e., someone writing me such a macro), but it's fine if someone wants to tell me how to write one. HELP

It seems that Word knows what I want to do and is purposefully thwarting me at every turn. Yes, it's what the shrinks would call paranoid thinking.

Guessed · #2 02-07-2025, 05:13 PM

I thought I'd give Gemini.AI a chance to write this code and it did a reasonable job that worked after a few fixes. Try this code which exports all modules as text files (rather than putting the code into a Word document). IMO exported modules is the more efficient way to save the code because you can then import them to a template rather than making a module and copying text in.

Code:

Sub ExportAllVBAModules()

  Dim vbProj As VBProject     'needs reference to Microsoft Visual Basic for Applications Extensibility 5.3
  Dim vbComp As VBComponent
  Dim strFileName As String, strFilePath As String

  ' Set the path where you want to save the exported modules.  ' You can change this to a specific folder or use a file dialog.
  strFilePath = Environ("USERPROFILE") & "\Documents\" ' Example: Saves to Documents folder

  Set vbProj = ThisDocument.VBProject  ' Get the current VBA project.
  For Each vbComp In vbProj.VBComponents  ' Loop through all components in the project.
    ' Check if the component is a module (standard module, class module, or form module).
    If vbComp.Type = vbext_ct_StdModule Or vbComp.Type = vbext_ct_ClassModule Or vbComp.Type = vbext_ct_MSForm Then

      ' Create the file name.  Use the component's name.        ' Replace invalid characters with underscores.
      strFileName = ReplaceInvalidChars(vbComp.Name) & ".bas"   ' Or .cls, .frm as needed.
      strFileName = strFilePath & strFileName ' Combine the path and file name.
      vbComp.Export strFileName    ' Export the component.
      Debug.Print "Exported: " & vbComp.Name & " to " & strFileName 'For debugging.
    End If
  Next vbComp

  MsgBox "All VBA modules exported to: " & strFilePath, vbInformation, "Export Complete"

End Sub

Private Function ReplaceInvalidChars(strName As String) As String
  ' Helper function to replace invalid characters in file names.
  Dim strInvalidChars As String, i As Long
  strInvalidChars = "<>:""/\|?*" ' Invalid characters for file names.
  For i = 1 To Len(strInvalidChars)
    strName = Replace(strName, Mid(strInvalidChars, i, 1), "_")
  Next i
  ReplaceInvalidChars = strName
End Function

PS. You wouldn't be so paranoid if there wasn't all those programmers out to get you

Dr. Rick · #3 02-08-2025, 08:39 AM

Thanks a lot!
I'll try it right away and apprise of success (or not).
And you're right, I'd be less paranoid if all those programmers weren't out to get me

RJ

Dr. Rick · #4 02-08-2025, 09:41 AM

It stopped immediately on the first line of code:

Dim vbProj As VBProject 'needs reference to Microsoft Visual Basic for Applications Extensibility 5.3

But your note seems to anticipate this very error. I don't know what "needs reference.. Extensibility 5.3" means.

I may be even more of a "novice" than my label would indicate.
RJ

Italophile · #5 02-08-2025, 01:06 PM

Quote:

Originally Posted by Dr. Rick

I don't know what "needs reference.. Extensibility 5.3" means.

To add a reference:

Open the Visual Basic Editor.
Ensure that the project you want to add the reference to is selected in the Project Explorer.
Go to the Tools menu and click on References.
Screenshot 2025-02-08 195801.png
In the dialog scroll down the list of Available References until you find the one you need, and check the box next to it.
Screenshot 2025-02-08 195900.png
Click OK

macropod · #6 02-09-2025, 04:51 AM

Quote:

Originally Posted by Dr. Rick

Since Word keeps arbitrarily and apparently randomly losing my macros, I am trying to record a macro that will, after going to "edit" in the list macros window, highlight all the text, copy it, exit and paste and save this to a blank document as a "backup."

So why don't you simply go into the VBE and export the code modules, forms, etc.? No code required. Plus you then also don't need any code to reimport them later.

gmaxey · #7 02-10-2025, 11:50 AM

I don't know the term for it (maybe self referencing) but I don't think you need to actually set a reference to the Applications Extensibility Library:

Code:

Sub ExportAllVBAModules()
'Late binding
Dim vbProj As Object
Dim vbComp As Object
Dim strFileName As String, strFilePath As String
Dim bExport As Boolean
  strFilePath = strFilePath = Environ("USERPROFILE") & "\Documents\" 'Path to export to.
  Set vbProj = ThisDocument.VBProject
  For Each vbComp In vbProj.VBComponents
    bExport = False
    Select Case vbComp.Type
      Case 1: strFileName = ReplaceInvalidChars(vbComp.Name) & ".bas": bExport = True
      Case 2: strFileName = ReplaceInvalidChars(vbComp.Name) & ".cls": bExport = True
      Case 3: strFileName = ReplaceInvalidChars(vbComp.Name) & ".frm": bExport = True
    End Select
    If bExport Then
      strFileName = strFilePath & strFileName
      vbComp.Export strFileName    ' Export the component.
      Debug.Print "Exported: " & vbComp.Name & " to " & strFileName
    End If
  Next vbComp
  MsgBox "All VBA modules exported to: " & strFilePath, vbInformation, "Export Complete"
lbl_Exit:
  Exit Sub
End Sub

Italophile · #8 02-10-2025, 12:14 PM

Quote:

Originally Posted by gmaxey

I don't know the term for it (maybe self referencing) but I don't think you need to actually set a reference to the Applications Extensibility Library

The term is: Late Binding

gmaxey · #9 02-10-2025, 12:20 PM

Yes, I understand it is late binding, but typically, with late binding I believe you have
to do something like:

Set vbProj = CreateObject(????????)

Where in this case we can user the object ThisDocument.VBProject without having a reference.

Italophile · #10 02-10-2025, 12:49 PM

Quote:

Originally Posted by gmaxey

Yes, I understand it is late binding, but typically, with late binding I believe you have
to do something like:

Set vbProj = CreateObject(????????)

Where in this case we can user the object ThisDocument.VBProject without having a reference.

Late binding refers to the use of a variable typed as a generic object which is only bound at the Set statement, which is the case in your code.

Italophile · #11 02-10-2025, 01:04 PM

Running either of the code examples here will generate an error:
Screenshot 2025-02-10 195119.png
unless you have given permission for VBA to access the code project

To grant permission:

File | Options | Trust Center | Trust Center Settings

Screenshot 2025-02-10 195903.png
Check the option to Trust access to the VBA project object model
Screenshot 2025-02-10 195740.png

NB: This is not generally considered a safe thing to do unless you really know what you are doing as it will also allow malware access.

macropod · #12 02-10-2025, 01:43 PM

Quote:

Originally Posted by Italophile

NB: This is not generally considered a safe thing to do unless you really know what you are doing as it will also allow malware access.

Absolutely! And, given that you'd really want to be granting access before running the macro then removing it immediately afterwards each time you want to do a backup, I fail to see what's gained over simply pressing Alt-F11 to open the VBE, then manually exporting the code & forms requiring backup.

Italophile · #13 02-10-2025, 03:16 PM

Quote:

Originally Posted by macropod

Absolutely! And, given that you'd really want to be granting access before running the macro then removing it immediately afterwards each time you want to do a backup, I fail to see what's gained over simply pressing Alt-F11 to open the VBE, then manually exporting the code & forms requiring backup.

Or, even better, prevent the issue from occurring in the first place by storing code in a template located in the user startup folder, instead of Normal.dotm.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word macro to cut a range of text and apply to subsequent text as a hyperlink	scientist101	Word VBA	9	07-20-2020 04:57 PM
Macro to add title in header is missing text once macro is run	shawnee24	Excel Programming	1	05-27-2015 11:50 PM
Macro to add title in header is missing text once macro is run	shawnee24	Word VBA	3	05-27-2015 12:35 PM
Microsoft Word macro to find text, select all text between brackets, and delete	helal1990	Word VBA	4	02-05-2015 03:52 PM
Macro to find coloured text and replace with form-field/formtext containing that text	tarktran	Word VBA	1	11-26-2014 08:12 AM