Export all modules from personal.xlsb

Question

I would like to export/ maintain/ manage a text file backup of modules in my personal macro workbook personal.xlsb using VBA.

I cannot find an object library which refers to the modules themselves on msdn. Could someone point me in the right direction on this please?

Using Excel 2013.

Good place to start: http://www.cpearson.com/Excel/VBE.aspx – Rory Aug 01 '16 at 09:14 — Rory, Aug 01 '16 at 09:14
@Downvoter, care to explain? – Preston Aug 01 '16 at 09:34 — Preston, Aug 01 '16 at 09:34

score 3 · Accepted Answer · answered Aug 01 '16 at 14:41

You need to add Visual Basic for Application Extensibility X.X reference; or:

Sub load_reference_1() 
ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
end sub

Sub Load_reference_2() 
ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
end sub

Example:

Sub Macromodule_copy1()
    ThisWorkbook.VBProject.VBComponents("Macroos").Export "E:\Macroos.bas"
     With Workbooks.Add

    .VBProject.VBComponents.Import "E:\Macroos.bas"
    End With
End Sub

Further examples and source: Snb-Vba -awesome examples!-

My apologies it took so long to accept this answer- it's exactly what i need, thankyou — Preston, Aug 26 '16 at 08:38

score 1 · Answer 2 · answered May 06 '22 at 10:18

I do exactly this, with my personal.xlsb and also with other macro workbooks. I save the text files into a "VBA" subdirectory and put them into version control to keep track of the changes. I was inspired by Mass importing modules & references in VBA which references https://www.rondebruin.nl/win/s9/win002.htm

I have a module called WriteBas containing this code:

Attribute VB_Name = "WriteBas"
Option Explicit

Sub WriteAllBas()
' Write all VBA modules as .bas files to the directory of ThisWorkbook.
' Implemented to make version control work smoothly for identifying changes.
' Designed to be called every time this workbook is saved,
'   if code has changed, then will show up as a diff
'   if code has not changed, then file will be same (no diff) with new date.
' Following https://stackoverflow.com/questions/55956116/mass-importing-modules-references-in-vba
'            which references https://www.rondebruin.nl/win/s9/win002.htm

Dim cmp As VBComponent, cmo As CodeModule
Dim fn As Integer, outName As String
Dim sLine As String, nLine As Long
Dim dirExport As String, outExt As String
Dim fileExport As String

   On Error GoTo MustTrustVBAProject
   Set cmp = ThisWorkbook.VBProject.VBComponents(1)
   On Error GoTo 0
   dirExport = ThisWorkbook.Path + Application.PathSeparator + "VBA" + Application.PathSeparator
   For Each cmp In ThisWorkbook.VBProject.VBComponents
      Select Case cmp.Type
         Case vbext_ct_ClassModule:
            outExt = ".cls"
         Case vbext_ct_MSForm
            outExt = ".frm"
         Case vbext_ct_StdModule
            outExt = ".bas"
         Case vbext_ct_Document
            Set cmo = cmp.CodeModule
            If Not cmo Is Nothing Then
               If cmo.CountOfLines = cmo.CountOfDeclarationLines Then ' Ordinary worksheet or Workbook, no code
                  outExt = ""
               Else ' It's a Worksheet or Workbook but has code, export it
                  outExt = ".cls"
               End If
            End If ' cmo Is Nothing
         Case Else
            Stop ' Debug it
      End Select
      If outExt <> "" Then
         fileExport = dirExport + cmp.name + outExt
         If Dir(fileExport) <> "" Then Kill fileExport   ' From Office 365, Export method does not overwrite existing file
         cmp.Export fileExport
      End If
   Next cmp
   Exit Sub
    
MustTrustVBAProject:
   MsgBox "Must trust VB Project in Options, Trust Center, Trust Center Settings ...", vbCritical + vbOKOnly, "WriteAllBas"
End Sub

and in my ThisWorkbook object, the BeforeSave event handler calls it each time the workbook is saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    WriteAllBas
End Sub

There's a second or two of overhead each time the workbook is saved.

Note: Under Office 2016 and earlier versions I didn't need to delete (Kill) the text file before exporting, but under Office 365 the Export method fails if the file exists.

score 0 · Answer 3 · answered Jun 14 '18 at 19:28

I just save a date/timestamped copy of PERSONAL.xlsb to a backup drive location using the following code.

Sub PersonalBckup() 

    Const dstBak      As String = "H:\PERSONAL MACROS\"   'change path to suit
    Const dstBak2     As String = "D:\PERSONAL Macros\"

      On Error Resume Next                      'if either of the drives are not present, skip error.
      Application.DisplayAlerts = False         'turn off warning popups
      With Workbooks("PERSONAL.xlsb")           'name of your PERSONAL.xlsb file
        .SaveCopyAs dstBak & "PERSONAL" & " as of " & Format(Now(), "YYYYMMDD_hhmmAMPM") & ".xlsb"
        .SaveCopyAs dstBak2 & "PERSONAL" & " as of " & Format(Now(), "YYYYMMDD_hhmmAMPM") & ".xlsb"
        .Save
      End With

Application.DisplayAlerts = True      'Turn on warning popups

The backed-up file is saved with a date/timestamp: "PERSONAL as of 20180512_0136PM.xlsb"

score 0 · Answer 4 · answered Oct 20 '22 at 14:52

I know this doesn't exactly answer the question, but perhaps it's still helpful. You can easily save all modules into a pdf by rigth clicking the modules folder and clicking print (and then clicking setup to change to print to pdf) . This won't give you a specific exported file that can be easily imported back in per se, but it keeps a safely saved file that you can always go back and reference should anything go wrong in your code. There's probably a way to automate this (or at least make it a one-time click when you save), but I haven't figured that out yet.

Export all modules from personal.xlsb

4 Answers4