Sync and/or backup Personal macro workbook

Marty Plante

New Member
Joined
Dec 28, 2016
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I want my personal macro workbook to reside, or be synchronized to, a location within SharePoint. I have searched off and on over the past 2 years and cannot find a decent response. My hopes are to store all VBA code in the Personal wb, but have that same code in my other pc's if not immediately at least not done manually.

My intent is to treat this as a "master VBA document" and secure it via a OneDrive sync to a SharePoint site.

Any ideas? Quite surprised I don't see discussion on this so maybe technical reasons make it impossible? Or if someone can connect me to posts where this is discussed I'll follow up.

Office 365 business environment. (Still some older W7 & W8 pc's)

Thanks,
Marty
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I've been looking for an answer to a similar version of this too; but, I'm not getting anywhere yet. Did you ever find a solution?
 
Upvote 0
I've been looking for an answer to a similar version of this too; but, I'm not getting anywhere yet. Did you ever find a solution?
I've looked into this on occasion but nothing obvious has ever come to my attention. Also, with moving to Office 365 that has changed some of the way that I keep templates and files for critical time saving pieces such as this. I have started using Notepad++ to work with editing code (I'm no guru, just do what I have to) and decided to default those simple text files to a SharePoint library that is sync'd so I always have a local and protected copy.
Second to that I have create a "master workbook" that I include the most common VBA code as well as some preformatted sheets that I commonly use. This may turn into 5 or more templates, all macro enabled that provide different types of features sort of by default.

My brain keeps saying "why isn't this like a custom.dic file for office programs. You get your standard sets of common code, and add your own as you go. Just make sure to copy and back it up.
 
Upvote 0
Marty: Thanks for your response. At least I'm not crazy or alone -- well, maybe we both are.

I really like your idea of the Master Workbook to hold code and sheet templates. I can see that as being a great way to store a documentation sheet for the workbook, constants as named ranges, conversion tables, and hyperlinks to the web as applicable. I wonder if content of some of those sheets could also be linked to master versions (i.e. you never know when they are going to change the value of Pi or something). The idea of something equivalent to Custom.dic for code is also really attractive. I hope Microsoft is taking notes.

I'm a personal user and don't have access to SharePoint that I'm aware of. I do love using OneNote to supplement my aging memory and recording solutions to various problems I've had. In one of my last pre-retirement projects, I worked on a coding project that I intended for a prospective client and I kept that code in its own Master Workbook and Master Document that would likely have ended up being a pair of Add-ins. I think I may go back with that approach rather than the Personal.xlsb and its Word equivalent. After all, who am I to mess around with someone's Personal.xlsb etc.

I still use the VBA editor to write and maintain my code to gain access to IntelliSense. As you may know, a lot of editing can introduce instability into code and can be re-stabilized by Exporting and Importing the VBA. Ron de Bruin had a couple of routines for doing this that he posted at Import and Export VBA code. I found this to be really effective in solving problems that get introduced by the compiler or some other ghost in the machine. I also found it to be a means for maintaining the same code in Excel and Word procedure libraries.

I adapted Ron's code for use with Word or Excel and generalized the code so it works with both applications without any real-time changes. I tend to work heavily in Word or heavily in Excel. Between bouts of heavy editing and after doing a RefreshModules on the newly edited code, I re-import all the code into the version for Word or Excel that has been superseded by the editing. I keep the following code in its own module that is stored independently from my procedure libraries. Redundantly keeping it in the procedure libraries for Excel/Word assures it is always available if my work migrates to another machine. I tried to make sure it is well documented and robust enough to work on a machine other than my own. YMMV.

Ron: Thanks for all you do. I hope this is in keeping with accepted use of your generous contributions.

VBA Code:
Option Explicit
' This code was derived and enhanced from VBA published by Ron de Bruin.
' Enhancements by Bruce Volkert circa 2018

' The Original Code, an explanation of its function, and usages is found at: _
https://www.rondebruin.nl/win/s9/win002.htm

' Function: _
  Copies all modules in the Active Document to and/or from a Target Folder on the user's computer. _
    Typically, this code creates and uses the VBAProjectFiles folder in the user's MyDocuments folder. _
      If this target folder does not exist it will be created. _
      If this target folder exists, the, Export will delete all files in the Target Folder _
        before Exporting modules from the Active Document.

' Enhancements: _
  Generalized for use with Excel and Word. _
  Configured for early or late binding as indicated by bLateBinding = True or False. _
  Refactored for consistency with personal naming convention.

' Conditions for use: _
  This code must be run from outside of the Active Document.

' Export: _
    If not present, creates the Target Folder (i.e. szExportPath). _
    Identifies the application of associated with the Active Document. _
    Exports all Modules (standard, class, and form) to the Target Folder.
    
' Import: _
    Verifies there are files to import in the Target Folder. _
    Deletes ALL Modules (standard, class, and form) from the Active Document. _
    Copies all Modules (standard, class, and form) from the Target Folder into the Active Document. _

' RefreshModules: _
    Runs Export _
    Runs Import
  
' Note: _
    Although a copy of this module may be included in the Active Document, it must be executed from another document. _
    Simply copy the module from the Active Document into any other document that is open (surrogate docuent). _
    Execute the Refresh/Export/Import procedures as warranted. _
    You can removed the module from the surrogate document if you wish; but, this is not necessary.
    
' Plans: _
    Add code and procedures to perform this function for additional MS Office Applications.

' Compiler Directives: _
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/const-directive
#Const bLateBinding = True    ' True means Use Late Binding. False means use Early Binding.

' References Required for EarlyBinding:
'   Microsoft Scripting Runtime _
    Microsoft Visual Basic for Applications Extensibility 5.3 library _
    Requires a reference to Microsoft Excel 14.0 Object library _
    Requires a reference to Microsoft Scripting Runtime

Private Sub RefreshModules()
  Export
  Import
End Sub

Private Sub Export()
  Select Case Application
    Case "Microsoft Excel"
      ExportModulesFromExcelWorkbook
    Case "Microsoft Word"
      ExportModulesFromWordDocument
  End Select
End Sub

Private Sub Import()
  Select Case Application
    Case "Microsoft Excel"
      ImportModulesToExcelWorkbook
    Case "Microsoft Word"
      ImportModulesToWordDocument
  End Select
End Sub

Private Sub ExportModulesFromWordDocument()

  #If bLateBinding Then
    Dim appWord As Object
    Dim MySource As Object
    Dim cmpComponent As Object
  #Else
    Dim appWord As Word.Application
    Dim MySource As Word.Document
    Dim cmpComponent As VBIDE.VBComponent ' Requires reference to _
      Microsoft Visual Basic for Applications Extensibility 5.3 library
  #End If

  On Error Resume Next
  Set appWord = GetObject(, "Word.Application")
  If Err Then
    MsgBox "Word is not running." & vbCr & _
      "There is nothing to Export from Word.", vbOKOnly
    Exit Sub
  End If
  On Error GoTo 0
  
  Dim bExport As Boolean
  Dim szSourceDocument As String
  Dim szExportPath As String
  Dim szFileName As String

  ''' The code modules will be exported in a folder named.
  ''' VBAProjectFiles in the Documents folder.
  ''' The code below create this folder if it not exist
  ''' or delete all files in the folder if it exist.
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Export Folder not exist"
    Exit Sub
  End If
    
  On Error Resume Next
  Kill FolderWithVBAProjectFiles & "\*.*"
  On Error GoTo 0

  ''' NOTE: This Document must be open in Word.
  szSourceDocument = appWord.ActiveDocument.Name
  Set MySource = appWord.Application.Documents(szSourceDocument)
    
  If MySource.VBProject.Protection = 1 Then
    MsgBox "The VBA in this Document is protected," & _
           "not possible to export the code"
    Exit Sub
  End If
    
  szExportPath = FolderWithVBAProjectFiles & "\"
    
  For Each cmpComponent In MySource.VBProject.VBComponents
        
    bExport = True
    szFileName = cmpComponent.Name

    ''' Concatenate the correct filename for export.
    Select Case cmpComponent.Type
      Case vbext_ct_ClassModule
        szFileName = szFileName & ".cls"
      Case vbext_ct_MSForm
        szFileName = szFileName & ".frm"
      Case vbext_ct_StdModule
        szFileName = szFileName & ".bas"
      Case vbext_ct_Document
        ''' This is a worksheet or Document object.
        ''' Don't try to export.
        bExport = False
    End Select
        
    If bExport Then
      ''' Export the component to a text file.
      cmpComponent.Export szExportPath & szFileName
      ''' remove it from the project if you want
      '''MySource.VBProject.VBComponents.Remove cmpComponent
    End If
  Next cmpComponent
  MsgBox "Export is ready"
End Sub

Private Sub ExportModulesFromExcelWorkbook()

  #If bLateBinding Then
    Dim appExcel As Object
    Dim wkbSource As Object
    Dim cmpComponent As Object
  #Else
    Dim appExcel As Excel.Application
    Dim wkbSource As Excel.Workbook
    Dim cmpComponent As VBIDE.VBComponent
  #End If

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    MsgBox "Excel is not running." & vbCr & _
      "There is nothing to Export from Excel.", vbOKOnly
    Exit Sub
  End If
  On Error GoTo 0
    
  Dim bExport As Boolean
  Dim szSourceWorkbook As String
  Dim szExportPath As String
  Dim szFileName As String

  ''' The code modules will be exported in a folder named.
  ''' VBAProjectFiles in the Documents folder.
  ''' The code below create this folder if it not exist
  ''' or delete all files in the folder if it exist.
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Export Folder not exist"
    Exit Sub
  End If
    
  On Error Resume Next
  Kill FolderWithVBAProjectFiles & "\*.*"
  On Error GoTo 0

  ''' NOTE: This workbook must be open in Excel.
  szSourceWorkbook = appExcel.ActiveWorkbook.Name
  Set wkbSource = appExcel.Application.Workbooks(szSourceWorkbook)
  If wkbSource.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
           "not possible to export the code"
    Exit Sub
  End If
  szExportPath = FolderWithVBAProjectFiles & "\"
  For Each cmpComponent In wkbSource.VBProject.VBComponents
    bExport = True
    szFileName = cmpComponent.Name
    ''' Concatenate the correct filename for export.
    Select Case cmpComponent.Type
      Case vbext_ct_ClassModule
        szFileName = szFileName & ".cls"
      Case vbext_ct_MSForm
        szFileName = szFileName & ".frm"
      Case vbext_ct_StdModule
        szFileName = szFileName & ".bas"
      Case vbext_ct_Document
        ''' This is a worksheet or workbook object.
        ''' Don't try to export.
        bExport = False
    End Select
    If bExport Then
      ''' Export the component to a text file.
      cmpComponent.Export szExportPath & szFileName
      ''' remove it from the project if you want
      '''wkbSource.VBProject.VBComponents.Remove cmpComponent
    End If
  Next cmpComponent
  MsgBox "Export is ready"
  
End Sub

Private Sub ImportModulesToExcelWorkbook()

  #If bLateBinding Then
    Dim appExcel As Object
    Dim wkbTarget As Object
    Dim objFSO As Object
    Dim objFile As Object
    Dim cmpComponents As Object
  #Else
    ' Requires a reference to Microsoft Excel 14.0 Object library
    Dim appExcel As Excel.Application
    Dim wkbTarget As Excel.Workbook
    ' Requires a reference to Microsoft Scripting Runtime
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
    Dim cmpComponents As VBIDE.VBComponents
  #End If

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    ' Excel was not running
    Set appExcel = CreateObject("Excel.Application")
  End If
  On Error GoTo 0
  
  Dim szTargetWorkbook As String
  Dim szImportPath As String
  Dim szFileName As String

  If ActiveWorkbook.Name = ThisWorkbook.Name Then
    MsgBox "Select another destination workbook" & _
           "Not possible to import in this workbook "
    Exit Sub
  End If
  'Get the path to the folder with modules
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Import Folder not exist"
    Exit Sub
  End If
  ''' NOTE: This workbook must be open in Excel.
  szTargetWorkbook = appExcel.ActiveWorkbook.Name
  Set wkbTarget = appExcel.Application.Workbooks(szTargetWorkbook)
  If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
           "not possible to Import the code"
    Exit Sub
  End If
  ''' NOTE: Path where the code modules are located.
  szImportPath = FolderWithVBAProjectFiles & "\"
  Set objFSO = New Scripting.FileSystemObject
  If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
    MsgBox "There are no files to import"
    Exit Sub
  End If
  'Delete all modules/Userforms from the ActiveWorkbook
  Call DeleteVBAModulesAndUserFormsFromExcelWorkbook
  Set cmpComponents = wkbTarget.VBProject.VBComponents
  ''' Import all the code modules in the specified path
  ''' to the ActiveWorkbook.
  For Each objFile In objFSO.GetFolder(szImportPath).Files
    If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
        (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
         (objFSO.GetExtensionName(objFile.Name) = "bas") Then
      cmpComponents.Import objFile.Path
    End If
  Next objFile
  MsgBox "Import is ready"
  
End Sub

Private Sub ImportModulesToWordDocument()

  #If bLateBinding Then
    Dim appWord As Object
    Dim cmpComponents As Object
    Dim MyTarget As Object
    Dim objFSO As Object
    Dim objFile As Object
  #Else
    Dim appWord As Word.Application
    Dim cmpComponents As VBIDE.VBComponents
    Dim MyTarget As Word.Document
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
  #End If

  On Error Resume Next
  Set appWord = GetObject(, "Word.Application")
  If Err Then
    ' Word was not running
    Set appWord = CreateObject("Word.Application")
  End If
  On Error GoTo 0
  
  Dim szMyTarget As String
  Dim szImportPath As String
  Dim szFileName As String

  #If Application = "Word.Application" Then
    If appWord.ActiveDocument.Name = ThisDocument.Name Then
      MsgBox "Select another destination Document" & _
             "Not possible to import in this Document "
      Exit Sub
    End If
  #End If
  'Get the path to the folder with modules
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Import Folder not exist"
    Exit Sub
  End If
  ''' NOTE: This Document must be open in Word.
  szMyTarget = appWord.ActiveDocument.Name
  Set MyTarget = appWord.Application.Documents(szMyTarget)
  If MyTarget.VBProject.Protection = 1 Then
    MsgBox "The VBA in this Document is protected," & _
           "not possible to Import the code"
    Exit Sub
  End If
  ''' NOTE: Path where the code modules are located.
  szImportPath = FolderWithVBAProjectFiles & "\"
  Set objFSO = New Scripting.FileSystemObject
  If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
    MsgBox "There are no files to import"
    Exit Sub
  End If
  'Delete all modules/Userforms from the ActiveDocument
  DeleteVBAModulesAndUserFormsFromWordDocument
  Set cmpComponents = MyTarget.VBProject.VBComponents
  ''' Import all the code modules in the specified path
  ''' to the ActiveDocument.
  For Each objFile In objFSO.GetFolder(szImportPath).Files
    If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
        (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
         (objFSO.GetExtensionName(objFile.Name) = "bas") Then
      cmpComponents.Import objFile.Path
    End If
  Next objFile
  MsgBox "Import is ready"
  
End Sub

Private Function FolderWithVBAProjectFiles() As String

  Dim WshShell As Object
  Dim FSO As Object
  Dim SpecialPath As String

  Set WshShell = CreateObject("WScript.Shell")
  Set FSO = CreateObject("scripting.filesystemobject")
  SpecialPath = WshShell.SpecialFolders("MyDocuments")
  If Right$(SpecialPath, 1) <> "\" Then
    SpecialPath = SpecialPath & "\"
  End If
  If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
    On Error Resume Next
    MkDir SpecialPath & "VBAProjectFiles"
    On Error GoTo 0
  End If
  If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
    FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
  Else
    FolderWithVBAProjectFiles = "Error"
  End If
    
End Function

Private Function DeleteVBAModulesAndUserFormsFromExcelWorkbook() As Variant

  #If bLateBinding Then
    Dim appExcel As Object
    Dim VBProj As Object
    Dim VBComp As Object
  #Else
    Dim appExcel As Excel.Application
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
  #End If

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    ' Excel was not running
    Set appExcel = CreateObject("Excel.Application")
  End If
  On Error GoTo 0
        
  Set VBProj = appExcel.ActiveWorkbook.VBProject
  For Each VBComp In VBProj.VBComponents
    If VBComp.Type = vbext_ct_Document Then
      'Thisworkbook or worksheet module
      'We do nothing
    Else
      VBProj.VBComponents.Remove VBComp
    End If
  Next VBComp
  
End Function

Private Function DeleteVBAModulesAndUserFormsFromWordDocument() As Variant

  #If bLateBinding Then
    Dim appWord As Object
    Dim VBProj As Object
    Dim VBComp As Object
  #Else
    Dim appWord As Word.Application
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
  #End If

  On Error Resume Next
  Set appWord = GetObject(, "Word.Application")
  If Err Then
    MsgBox "Word is not running." & vbCr & _
      "There is nothing to Delete from Word.", vbOKOnly
    Exit Function
  End If
  On Error GoTo 0
        
  Set VBProj = appWord.ActiveDocument.VBProject
  For Each VBComp In VBProj.VBComponents
    If VBComp.Type = vbext_ct_Document Then
      'Thisworkbook or worksheet module
      'We do nothing
    Else
      VBProj.VBComponents.Remove VBComp
    End If
  Next VBComp
  
End Function
 
Upvote 0
Very interesting, much of what you do is beyond my knowledge level. I've picked up bits and pieces along the way and it's far more about time saving steps that are easy to remember and create the habit of using them. OneNote is great but I've struggled to get into the habit of using it. The idea of using it to store code had not occurred to me. Using a text editor wipes any formatting, an excel sheet formatted text only would do the same, I suppose, but would this be a concern in OneNote? Typical of my experience with Microsoft, once the create a feature it can be near impossible to turn the darn thing off.... Maybe this isn't an issue but I wouldn't want working hyperlinks and all that in something specifically for code. Could be I'm concerned about nothing.

This is an old thread but I'll pose the question to a few friends who write code and manage databases that I've met over the past couple years and see what I learn. I'll post any information I stumble across.


Marty.
 
Upvote 0
Oops. My bad! You wrote NotePad++. I've been poking around in OneNote for very non-code reasons and I guess my brain I just registered OneNote instead of NotePad++. Sorry for the confusion. OneNote is great for personal notes. I use it as a personal blog.

I doubt that what I do is beyond you. I mostly search the web and adapt what I find to my applications. I do try to generalize the procedures I find; but, most of it is enhanced re-tilling the ground others have previously plowed.

I totally agree that OneNote is not an appropriate vehicle for storing code. I tried that for a day or two a long time ago and abandoned the idea then. I also tried NotePad++, VIM, and Visual Studio; but really did not give any of them much of a chance. Writing and storing code in the Office VBA world is what I settled on at least for the time being.

It'll be interesting to read what you learn from your friends. Thanks for your interest.
 
Last edited:
Upvote 0
Marty: Thanks for your response. At least I'm not crazy or alone -- well, maybe we both are.

I really like your idea of the Master Workbook to hold code and sheet templates. I can see that as being a great way to store a documentation sheet for the workbook, constants as named ranges, conversion tables, and hyperlinks to the web as applicable. I wonder if content of some of those sheets could also be linked to master versions (i.e. you never know when they are going to change the value of Pi or something). The idea of something equivalent to Custom.dic for code is also really attractive. I hope Microsoft is taking notes.

I'm a personal user and don't have access to SharePoint that I'm aware of. I do love using OneNote to supplement my aging memory and recording solutions to various problems I've had. In one of my last pre-retirement projects, I worked on a coding project that I intended for a prospective client and I kept that code in its own Master Workbook and Master Document that would likely have ended up being a pair of Add-ins. I think I may go back with that approach rather than the Personal.xlsb and its Word equivalent. After all, who am I to mess around with someone's Personal.xlsb etc.

I still use the VBA editor to write and maintain my code to gain access to IntelliSense. As you may know, a lot of editing can introduce instability into code and can be re-stabilized by Exporting and Importing the VBA. Ron de Bruin had a couple of routines for doing this that he posted at Import and Export VBA code. I found this to be really effective in solving problems that get introduced by the compiler or some other ghost in the machine. I also found it to be a means for maintaining the same code in Excel and Word procedure libraries.

I adapted Ron's code for use with Word or Excel and generalized the code so it works with both applications without any real-time changes. I tend to work heavily in Word or heavily in Excel. Between bouts of heavy editing and after doing a RefreshModules on the newly edited code, I re-import all the code into the version for Word or Excel that has been superseded by the editing. I keep the following code in its own module that is stored independently from my procedure libraries. Redundantly keeping it in the procedure libraries for Excel/Word assures it is always available if my work migrates to another machine. I tried to make sure it is well documented and robust enough to work on a machine other than my own. YMMV.

Ron: Thanks for all you do. I hope this is in keeping with accepted use of your generous contributions.

VBA Code:
Option Explicit
' This code was derived and enhanced from VBA published by Ron de Bruin.
' Enhancements by Bruce Volkert circa 2018

' The Original Code, an explanation of its function, and usages is found at: _
https://www.rondebruin.nl/win/s9/win002.htm

' Function: _
  Copies all modules in the Active Document to and/or from a Target Folder on the user's computer. _
    Typically, this code creates and uses the VBAProjectFiles folder in the user's MyDocuments folder. _
      If this target folder does not exist it will be created. _
      If this target folder exists, the, Export will delete all files in the Target Folder _
        before Exporting modules from the Active Document.

' Enhancements: _
  Generalized for use with Excel and Word. _
  Configured for early or late binding as indicated by bLateBinding = True or False. _
  Refactored for consistency with personal naming convention.

' Conditions for use: _
  This code must be run from outside of the Active Document.

' Export: _
    If not present, creates the Target Folder (i.e. szExportPath). _
    Identifies the application of associated with the Active Document. _
    Exports all Modules (standard, class, and form) to the Target Folder.
   
' Import: _
    Verifies there are files to import in the Target Folder. _
    Deletes ALL Modules (standard, class, and form) from the Active Document. _
    Copies all Modules (standard, class, and form) from the Target Folder into the Active Document. _

' RefreshModules: _
    Runs Export _
    Runs Import
 
' Note: _
    Although a copy of this module may be included in the Active Document, it must be executed from another document. _
    Simply copy the module from the Active Document into any other document that is open (surrogate docuent). _
    Execute the Refresh/Export/Import procedures as warranted. _
    You can removed the module from the surrogate document if you wish; but, this is not necessary.
   
' Plans: _
    Add code and procedures to perform this function for additional MS Office Applications.

' Compiler Directives: _
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/const-directive
#Const bLateBinding = True    ' True means Use Late Binding. False means use Early Binding.

' References Required for EarlyBinding:
'   Microsoft Scripting Runtime _
    Microsoft Visual Basic for Applications Extensibility 5.3 library _
    Requires a reference to Microsoft Excel 14.0 Object library _
    Requires a reference to Microsoft Scripting Runtime

Private Sub RefreshModules()
  Export
  Import
End Sub

Private Sub Export()
  Select Case Application
    Case "Microsoft Excel"
      ExportModulesFromExcelWorkbook
    Case "Microsoft Word"
      ExportModulesFromWordDocument
  End Select
End Sub

Private Sub Import()
  Select Case Application
    Case "Microsoft Excel"
      ImportModulesToExcelWorkbook
    Case "Microsoft Word"
      ImportModulesToWordDocument
  End Select
End Sub

Private Sub ExportModulesFromWordDocument()

  #If bLateBinding Then
    Dim appWord As Object
    Dim MySource As Object
    Dim cmpComponent As Object
  #Else
    Dim appWord As Word.Application
    Dim MySource As Word.Document
    Dim cmpComponent As VBIDE.VBComponent ' Requires reference to _
      Microsoft Visual Basic for Applications Extensibility 5.3 library
  #End If

  On Error Resume Next
  Set appWord = GetObject(, "Word.Application")
  If Err Then
    MsgBox "Word is not running." & vbCr & _
      "There is nothing to Export from Word.", vbOKOnly
    Exit Sub
  End If
  On Error GoTo 0
 
  Dim bExport As Boolean
  Dim szSourceDocument As String
  Dim szExportPath As String
  Dim szFileName As String

  ''' The code modules will be exported in a folder named.
  ''' VBAProjectFiles in the Documents folder.
  ''' The code below create this folder if it not exist
  ''' or delete all files in the folder if it exist.
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Export Folder not exist"
    Exit Sub
  End If
   
  On Error Resume Next
  Kill FolderWithVBAProjectFiles & "\*.*"
  On Error GoTo 0

  ''' NOTE: This Document must be open in Word.
  szSourceDocument = appWord.ActiveDocument.Name
  Set MySource = appWord.Application.Documents(szSourceDocument)
   
  If MySource.VBProject.Protection = 1 Then
    MsgBox "The VBA in this Document is protected," & _
           "not possible to export the code"
    Exit Sub
  End If
   
  szExportPath = FolderWithVBAProjectFiles & "\"
   
  For Each cmpComponent In MySource.VBProject.VBComponents
       
    bExport = True
    szFileName = cmpComponent.Name

    ''' Concatenate the correct filename for export.
    Select Case cmpComponent.Type
      Case vbext_ct_ClassModule
        szFileName = szFileName & ".cls"
      Case vbext_ct_MSForm
        szFileName = szFileName & ".frm"
      Case vbext_ct_StdModule
        szFileName = szFileName & ".bas"
      Case vbext_ct_Document
        ''' This is a worksheet or Document object.
        ''' Don't try to export.
        bExport = False
    End Select
       
    If bExport Then
      ''' Export the component to a text file.
      cmpComponent.Export szExportPath & szFileName
      ''' remove it from the project if you want
      '''MySource.VBProject.VBComponents.Remove cmpComponent
    End If
  Next cmpComponent
  MsgBox "Export is ready"
End Sub

Private Sub ExportModulesFromExcelWorkbook()

  #If bLateBinding Then
    Dim appExcel As Object
    Dim wkbSource As Object
    Dim cmpComponent As Object
  #Else
    Dim appExcel As Excel.Application
    Dim wkbSource As Excel.Workbook
    Dim cmpComponent As VBIDE.VBComponent
  #End If

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    MsgBox "Excel is not running." & vbCr & _
      "There is nothing to Export from Excel.", vbOKOnly
    Exit Sub
  End If
  On Error GoTo 0
   
  Dim bExport As Boolean
  Dim szSourceWorkbook As String
  Dim szExportPath As String
  Dim szFileName As String

  ''' The code modules will be exported in a folder named.
  ''' VBAProjectFiles in the Documents folder.
  ''' The code below create this folder if it not exist
  ''' or delete all files in the folder if it exist.
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Export Folder not exist"
    Exit Sub
  End If
   
  On Error Resume Next
  Kill FolderWithVBAProjectFiles & "\*.*"
  On Error GoTo 0

  ''' NOTE: This workbook must be open in Excel.
  szSourceWorkbook = appExcel.ActiveWorkbook.Name
  Set wkbSource = appExcel.Application.Workbooks(szSourceWorkbook)
  If wkbSource.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
           "not possible to export the code"
    Exit Sub
  End If
  szExportPath = FolderWithVBAProjectFiles & "\"
  For Each cmpComponent In wkbSource.VBProject.VBComponents
    bExport = True
    szFileName = cmpComponent.Name
    ''' Concatenate the correct filename for export.
    Select Case cmpComponent.Type
      Case vbext_ct_ClassModule
        szFileName = szFileName & ".cls"
      Case vbext_ct_MSForm
        szFileName = szFileName & ".frm"
      Case vbext_ct_StdModule
        szFileName = szFileName & ".bas"
      Case vbext_ct_Document
        ''' This is a worksheet or workbook object.
        ''' Don't try to export.
        bExport = False
    End Select
    If bExport Then
      ''' Export the component to a text file.
      cmpComponent.Export szExportPath & szFileName
      ''' remove it from the project if you want
      '''wkbSource.VBProject.VBComponents.Remove cmpComponent
    End If
  Next cmpComponent
  MsgBox "Export is ready"
 
End Sub

Private Sub ImportModulesToExcelWorkbook()

  #If bLateBinding Then
    Dim appExcel As Object
    Dim wkbTarget As Object
    Dim objFSO As Object
    Dim objFile As Object
    Dim cmpComponents As Object
  #Else
    ' Requires a reference to Microsoft Excel 14.0 Object library
    Dim appExcel As Excel.Application
    Dim wkbTarget As Excel.Workbook
    ' Requires a reference to Microsoft Scripting Runtime
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
    Dim cmpComponents As VBIDE.VBComponents
  #End If

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    ' Excel was not running
    Set appExcel = CreateObject("Excel.Application")
  End If
  On Error GoTo 0
 
  Dim szTargetWorkbook As String
  Dim szImportPath As String
  Dim szFileName As String

  If ActiveWorkbook.Name = ThisWorkbook.Name Then
    MsgBox "Select another destination workbook" & _
           "Not possible to import in this workbook "
    Exit Sub
  End If
  'Get the path to the folder with modules
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Import Folder not exist"
    Exit Sub
  End If
  ''' NOTE: This workbook must be open in Excel.
  szTargetWorkbook = appExcel.ActiveWorkbook.Name
  Set wkbTarget = appExcel.Application.Workbooks(szTargetWorkbook)
  If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
           "not possible to Import the code"
    Exit Sub
  End If
  ''' NOTE: Path where the code modules are located.
  szImportPath = FolderWithVBAProjectFiles & "\"
  Set objFSO = New Scripting.FileSystemObject
  If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
    MsgBox "There are no files to import"
    Exit Sub
  End If
  'Delete all modules/Userforms from the ActiveWorkbook
  Call DeleteVBAModulesAndUserFormsFromExcelWorkbook
  Set cmpComponents = wkbTarget.VBProject.VBComponents
  ''' Import all the code modules in the specified path
  ''' to the ActiveWorkbook.
  For Each objFile In objFSO.GetFolder(szImportPath).Files
    If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
        (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
         (objFSO.GetExtensionName(objFile.Name) = "bas") Then
      cmpComponents.Import objFile.Path
    End If
  Next objFile
  MsgBox "Import is ready"
 
End Sub

Private Sub ImportModulesToWordDocument()

  #If bLateBinding Then
    Dim appWord As Object
    Dim cmpComponents As Object
    Dim MyTarget As Object
    Dim objFSO As Object
    Dim objFile As Object
  #Else
    Dim appWord As Word.Application
    Dim cmpComponents As VBIDE.VBComponents
    Dim MyTarget As Word.Document
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
  #End If

  On Error Resume Next
  Set appWord = GetObject(, "Word.Application")
  If Err Then
    ' Word was not running
    Set appWord = CreateObject("Word.Application")
  End If
  On Error GoTo 0
 
  Dim szMyTarget As String
  Dim szImportPath As String
  Dim szFileName As String

  #If Application = "Word.Application" Then
    If appWord.ActiveDocument.Name = ThisDocument.Name Then
      MsgBox "Select another destination Document" & _
             "Not possible to import in this Document "
      Exit Sub
    End If
  #End If
  'Get the path to the folder with modules
  If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Import Folder not exist"
    Exit Sub
  End If
  ''' NOTE: This Document must be open in Word.
  szMyTarget = appWord.ActiveDocument.Name
  Set MyTarget = appWord.Application.Documents(szMyTarget)
  If MyTarget.VBProject.Protection = 1 Then
    MsgBox "The VBA in this Document is protected," & _
           "not possible to Import the code"
    Exit Sub
  End If
  ''' NOTE: Path where the code modules are located.
  szImportPath = FolderWithVBAProjectFiles & "\"
  Set objFSO = New Scripting.FileSystemObject
  If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
    MsgBox "There are no files to import"
    Exit Sub
  End If
  'Delete all modules/Userforms from the ActiveDocument
  DeleteVBAModulesAndUserFormsFromWordDocument
  Set cmpComponents = MyTarget.VBProject.VBComponents
  ''' Import all the code modules in the specified path
  ''' to the ActiveDocument.
  For Each objFile In objFSO.GetFolder(szImportPath).Files
    If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
        (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
         (objFSO.GetExtensionName(objFile.Name) = "bas") Then
      cmpComponents.Import objFile.Path
    End If
  Next objFile
  MsgBox "Import is ready"
 
End Sub

Private Function FolderWithVBAProjectFiles() As String

  Dim WshShell As Object
  Dim FSO As Object
  Dim SpecialPath As String

  Set WshShell = CreateObject("WScript.Shell")
  Set FSO = CreateObject("scripting.filesystemobject")
  SpecialPath = WshShell.SpecialFolders("MyDocuments")
  If Right$(SpecialPath, 1) <> "\" Then
    SpecialPath = SpecialPath & "\"
  End If
  If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
    On Error Resume Next
    MkDir SpecialPath & "VBAProjectFiles"
    On Error GoTo 0
  End If
  If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
    FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
  Else
    FolderWithVBAProjectFiles = "Error"
  End If
   
End Function

Private Function DeleteVBAModulesAndUserFormsFromExcelWorkbook() As Variant

  #If bLateBinding Then
    Dim appExcel As Object
    Dim VBProj As Object
    Dim VBComp As Object
  #Else
    Dim appExcel As Excel.Application
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
  #End If

  On Error Resume Next
  Set appExcel = GetObject(, "Excel.Application")
  If Err Then
    ' Excel was not running
    Set appExcel = CreateObject("Excel.Application")
  End If
  On Error GoTo 0
       
  Set VBProj = appExcel.ActiveWorkbook.VBProject
  For Each VBComp In VBProj.VBComponents
    If VBComp.Type = vbext_ct_Document Then
      'Thisworkbook or worksheet module
      'We do nothing
    Else
      VBProj.VBComponents.Remove VBComp
    End If
  Next VBComp
 
End Function

Private Function DeleteVBAModulesAndUserFormsFromWordDocument() As Variant

  #If bLateBinding Then
    Dim appWord As Object
    Dim VBProj As Object
    Dim VBComp As Object
  #Else
    Dim appWord As Word.Application
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
  #End If

  On Error Resume Next
  Set appWord = GetObject(, "Word.Application")
  If Err Then
    MsgBox "Word is not running." & vbCr & _
      "There is nothing to Delete from Word.", vbOKOnly
    Exit Function
  End If
  On Error GoTo 0
       
  Set VBProj = appWord.ActiveDocument.VBProject
  For Each VBComp In VBProj.VBComponents
    If VBComp.Type = vbext_ct_Document Then
      'Thisworkbook or worksheet module
      'We do nothing
    Else
      VBProj.VBComponents.Remove VBComp
    End If
  Next VBComp
 
End Function
How can I get that code working?

That code may be a solution to my problem - even if I do not understand much about it.
There is "Conditions for use: This code must be run from outside of the Active Document."
Therefore, I opened a new workbook and copied that code in a standard module there.
Activated my own excel workbook and tried to run a macro from a code above but I did not see any of those macros in the list of macros - maybe because they are private sub's -
"Private Sub Export()" and "Private Sub Import()" etc
Wondering if I should remove the word Private from the headings of the subs or would that have some harmful effect.
1617710639670.png
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top