Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-07-2025, 06:54 AM
Dr. Rick Dr. Rick is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2010
Advanced Beginner
macro to backup my VBA/macro text
 
Join Date: Apr 2023
Location: Coastal N.C.
Posts: 32
Dr. Rick is on a distinguished road
Question macro to backup my VBA/macro text

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.
Reply With Quote
  #2  
Old 02-07-2025, 05:13 PM
Guessed's Avatar
Guessed Guessed is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 4,166
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 02-08-2025, 08:39 AM
Dr. Rick Dr. Rick is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2010
Advanced Beginner
macro to backup my VBA/macro text
 
Join Date: Apr 2023
Location: Coastal N.C.
Posts: 32
Dr. Rick is on a distinguished road
Default Thanks for your help.

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
Reply With Quote
  #4  
Old 02-08-2025, 09:41 AM
Dr. Rick Dr. Rick is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2010
Advanced Beginner
macro to backup my VBA/macro text
 
Join Date: Apr 2023
Location: Coastal N.C.
Posts: 32
Dr. Rick is on a distinguished road
Default

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

Last edited by Dr. Rick; 02-08-2025 at 09:46 AM. Reason: clarity
Reply With Quote
  #5  
Old 02-08-2025, 01:06 PM
Italophile Italophile is offline macro to backup my VBA/macro text Windows 11 macro to backup my VBA/macro text Office 2021
Expert
 
Join Date: Mar 2022
Posts: 542
Italophile is just really niceItalophile is just really niceItalophile is just really niceItalophile is just really nice
Default

Quote:
Originally Posted by Dr. Rick View Post
I don't know what "needs reference.. Extensibility 5.3" means.
To add a reference:
  1. Open the Visual Basic Editor.
  2. Ensure that the project you want to add the reference to is selected in the Project Explorer.
  3. Go to the Tools menu and click on References.
    Screenshot 2025-02-08 195801.png
  4. 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
  5. Click OK
Reply With Quote
  #6  
Old 02-09-2025, 04:51 AM
macropod's Avatar
macropod macropod is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,384
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Dr. Rick View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 02-10-2025, 11:50 AM
gmaxey gmaxey is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2019
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,601
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #8  
Old 02-10-2025, 12:14 PM
Italophile Italophile is offline macro to backup my VBA/macro text Windows 11 macro to backup my VBA/macro text Office 2021
Expert
 
Join Date: Mar 2022
Posts: 542
Italophile is just really niceItalophile is just really niceItalophile is just really niceItalophile is just really nice
Default

Quote:
Originally Posted by gmaxey View Post
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
Reply With Quote
  #9  
Old 02-10-2025, 12:20 PM
gmaxey gmaxey is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2019
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,601
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

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.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #10  
Old 02-10-2025, 12:49 PM
Italophile Italophile is offline macro to backup my VBA/macro text Windows 11 macro to backup my VBA/macro text Office 2021
Expert
 
Join Date: Mar 2022
Posts: 542
Italophile is just really niceItalophile is just really niceItalophile is just really niceItalophile is just really nice
Default

Quote:
Originally Posted by gmaxey View Post
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.
Reply With Quote
  #11  
Old 02-10-2025, 01:04 PM
Italophile Italophile is offline macro to backup my VBA/macro text Windows 11 macro to backup my VBA/macro text Office 2021
Expert
 
Join Date: Mar 2022
Posts: 542
Italophile is just really niceItalophile is just really niceItalophile is just really niceItalophile is just really nice
Default

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:
  1. File | Options | Trust Center | Trust Center Settings

    Screenshot 2025-02-10 195903.png
  2. 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.
Reply With Quote
  #12  
Old 02-10-2025, 01:43 PM
macropod's Avatar
macropod macropod is offline macro to backup my VBA/macro text Windows 10 macro to backup my VBA/macro text Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,384
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Italophile View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 02-10-2025, 03:16 PM
Italophile Italophile is offline macro to backup my VBA/macro text Windows 11 macro to backup my VBA/macro text Office 2021
Expert
 
Join Date: Mar 2022
Posts: 542
Italophile is just really niceItalophile is just really niceItalophile is just really niceItalophile is just really nice
Default

Quote:
Originally Posted by macropod View Post
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.
Reply With Quote
Reply

Tags
backup/macros, vba save



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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft