Exporting all modules as text

[Rudi]

Hi,

I found the following code from here...

I am trying to run it from my personal macro workbook to export all the modules of code for backup purposes but am getting a persistent error. See image?
I thought it might be a ref library issue?

Any assistance or ideas?

Tx

1.jpg

2.jpg

[HansV]

Do you have a workbook open when you run the code? The code exports modules from the active workbook.

[Rudi]

Do you have a workbook open when you run the code? The code exports modules from the active workbook.

Hi,

No. Excel is open simply to access the VB editor.
Then I run the code from the module in the Personal.xlsb file (which contains all the code I want to export.)

Edit...just before posting this, I unhid the Personal File and ran it...and it worked... Your reply suddenly triggered..."ACTIVE" workbook

TX

[HansV]

If you want to run it on Personal.xlsb while it is hidden, change ActiveWorkbook to ThisWorkbook.

[Rudi]

I modified the code to only export the modules and also save it in TXT format instead of bas.

Here is the modified code...

Code: Select all

Public Sub ExportAllComponents()
    Dim VBComp As VBIDE.VBComponent
    Dim destDir As String, fName As String, ext As String
    destDir = "C:\Users\rudis\Desktop" 'ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & " Modules"
    If Dir(destDir, vbDirectory) = vbNullString Then MkDir destDir
    
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        If VBComp.CodeModule.CountOfLines > 0 Then
            Select Case VBComp.Type
                Case vbext_ct_StdModule: ext = ".bas"
                Case Else: ext = vbNullString
            End Select
            If ext <> vbNullString Then
                fName = destDir & "\" & VBComp.Name & ".txt"
                If Dir(fName, vbNormal) <> vbNullString Then Kill (fName)
                VBComp.Export (fName)
            End If
        End If
    Next VBComp
End Sub

[Rudi]

If you want to run it on Personal.xlsb while it is hidden, change ActiveWorkbook to ThisWorkbook.

Ah...tx. That will be useful!

[HansV]

In general, ThisWorkbook refers to the workbook that contains the code you are running.

[LisaGreen]

Does this help???

Code: Select all

Sub subExportProjectModulesToOneTextFile( _
            vbpProject As VBProject _
            )
' EXPORT All modules in a specified PROJECT
'  To a single text file.
' NO REPORT.
' END MESSAGE.
'
' Running this will export ALL modules in a project
'  to a SINGLE TXT file.
'
' See the comments in subImportAllModules before running that.
'
' File is optionally prefixed by
'  a DateTime Stamp.
'
' Output file names...
' DateTime Project.Ext.
'
' ### Note that the Modules are NOT sorted.
'

Dim blnlProject As Boolean
Dim blnlTDStamp As Boolean
Dim ilExported As Integer
Dim blnlExported As Boolean
Dim slFileName As String
Dim slModuleName As String
Dim vbclComponent As VBIDE.vbComponent
Dim slPrefix As String
Dim slProject As String
Dim vbplProject As Variant
Dim ilAddedProcs As Integer
Dim ilProcCount As Integer
Dim slNow As String
Dim slFolder As String
Dim tslTextStream As TextStream
Dim OLfso As FileSystemObject
Dim slDQ As String
Dim slProjectTextFilename As String
Dim vbcmlCodeModule As VBIDE.CodeModule
Dim slWriteLine As String
Dim lnglLine As Long
Dim ilN As Integer
Dim slCode As String
Dim lnglProcType As Long

slFolder = fncBrowseDlg()

If slFolder = "" Then
  MsgBox "@Need a folder."
  Exit Sub
End If

slProjectTextFilename = vbpProject.Name

slDQ = Chr(34)
Set OLfso = CreateObject("Scripting.FileSystemObject")

blnlTDStamp = fncYesNo(fncReadINIValue("Options", "ProjectExportTDStamp"))

If blnlTDStamp = True Then
  slNow = Format(Now(), "yyyymmddhhmmss")
Else
  slNow = ""
End If
slProjectTextFilename = slFolder & "\" _
        & Trim(slNow & " ") _
        & vbpProject.Name _
        & ".txt"

' Create an empty PROJECT text file.
' Write data to text file.
' Appending = 8.
' Read = 1.
' Write = 2
Set tslTextStream = OLfso.CreateTextFile(slProjectTextFilename, True)
tslTextStream.Close

' ... And open it.
Set tslTextStream = OLfso.OpenTextFile _
      (slProjectTextFilename, 8, True)

ilExported = 0
' Assume the project is NOT locked.
    
'Set VBComp = VBProj.VBComponents("Module1")
'Set CodeMod = VBComp.CodeModule

' Write a header.
slWriteLine = vbNewLine
slWriteLine = "@Number of modules :" & vbpProject.VBComponents.Count
slWriteLine = slWriteLine & vbNewLine

slWriteLine = slWriteLine & String(50, "-")
tslTextStream.WriteLine (slWriteLine)

For Each vbclComponent In vbpProject.VBComponents
  Select Case vbclComponent.Type
  Case vbext_ct_MSForm, _
          vbext_ct_StdModule, _
          vbext_ct_ClassModule, _
          vbext_ct_Document

    ' Check out the component
    Set vbcmlCodeModule = vbclComponent.CodeModule
    slModuleName = vbclComponent.Name
 
    With vbcmlCodeModule
      lnglLine = .CountOfDeclarationLines + 1
      Do Until lnglLine >= .CountOfLines
        slCode = .ProcOfLine(lnglLine, lnglProcType)
        '.ProcOfLine(intlStartLine, vbext_pk_Proc)
        'lnglLine = lnglLine + .ProcCountLines(slCode, vbclComponent.Type)
        lnglLine = lnglLine + .ProcCountLines(slCode, lnglProcType)
        ilN = ilN + 1
      Loop
 
      ' Write a header.
      slWriteLine = vbNewLine
      slWriteLine = slWriteLine & slModuleName
      slWriteLine = slWriteLine & vbNewLine
      slWriteLine = slWriteLine & "@Declaration lines :" & .CountOfDeclarationLines
      slWriteLine = slWriteLine & vbNewLine
      slWriteLine = slWriteLine & "@Total lines :" & .CountOfLines
      slWriteLine = slWriteLine & vbNewLine
      slWriteLine = slWriteLine & "@Number of procedures :" & ilN
      slWriteLine = slWriteLine & vbNewLine
      
      slWriteLine = slWriteLine & String(50, "-")
      tslTextStream.WriteLine (slWriteLine)
    
      slWriteLine = vbNewLine
      slCode = .Lines(1, .CountOfLines + 1)
      slWriteLine = slWriteLine & slCode
      
      tslTextStream.WriteLine (slWriteLine)
    
    End With
  End Select
  ilExported = ilExported + 1
Next vbclComponent
    
tslTextStream.Close
Set tslTextStream = Nothing
Set OLfso = Nothing

Debug.Print slFolder
Debug.Print slProjectTextFilename

subEndMessage CStr(ilExported) & " Exported" & vbCrLf & slProjectTextFilename
' *********************************************************************
End Sub

[LisaGreen]

Apologies... it seems all of the indenting has been removed.

Lisa

[HansV]

I have added [code] and [/code] tags around your code. This preserves the indenting and also reduces the space used by the code.

[Rudi]

Hi Lisa,

TX for the code.
Just having a problem testing it out?
What/How do I pass the argument. The project name does not work...not sure if it must pass as a string??
Any ideas?

1.jpg

[HansV]

The argument to subExportProjectModulesToOneTextFile is a VBProject object, not a string.

But I'm afraid the code is incomplete - it refers to other procedures or functions that aren't included: fncBrowseDlg, fncYesNo, fncReadINIValue and subEndMessage. Without those you cannot run the code.

[Rudi]

No problems.
The code I posted up the thread is working perfectly for my needs.

[LisaGreen]

Thanks for adding the code tags Hans.... and apologies for not posting the other procedures. If Anyone would like to get this working I'll get them all together in a workbook and post that. Please let me know if anyone would like me to do that. Alternatively people may download that code and much more at the codecage.

I just thought the code as it was would give some ideas.

Hugs
Lisa

[Rudi]

Thanks for volunteering to do this Lisa. If you don't mind, and it's not too much effort, it could be helpful for other users who might desire that functionality. The code I use, exports each module separately, which for my purposes is perfect as i group my code into separate modules and use some modules in multiple files. So exporting it all in one big text file will not work for me. However, as I mentioned, it can be useful for someone else, so feel free to post the full code since it is very much in context with this thread.

TX

[LisaGreen]

Hi Rudi,

Here is a "stand Alone" version of the code.

I've done my best to include the procedures being called as Private one and I've altered some lines to hard code rather than use too much from my add in.
Please don't shout at me if it doesn't work.

The sub subExportActiveProjectModulesToOneTextFile will save the *active* project. To use it....
1) Be in the project you want to export.
2) Open the Macros menu.
3) Change the Macros In: dropdown at the bottom to the workbook with the macros in.
4) Run subExportActiveProjectModulesToOneTextFile.

The sub subExportAllModules will export all modules in all projects.

The sub subImportAllModules will import all modules it finds in a prompted for folder.
This is so you can select the modules you need and delete the others or just copy modules you need to a separate folder and delete it after.
This means that you can save all your modules in one place.

HTH

Lisa

[Rudi]

Hi Lisa,

That is a massive bunch of code....

However, I had to comment out this line:

Code: Select all

blnlTDStamp = fncYesNo(fncReadINIValue("Options", "ProjectExportTDStamp"))

and as I did, the code worked fine and exported all modules in the current workbook to a text file.

I'm sure that if anyone needs this code, they can weed out the functionality they don't need to lessen the volume of code in the file.
TX for the contributions to this thread

[LisaGreen]

You're more than welcome Rudi!!

I must have missed that line... sorry . It should be set to true or false and controls whether a date/time "stamp" is added to the filename.

I notice also that I've screwed up trying to set it explicitly in that in the copy and paste I've not uncommented module lines 56 and 57.

If the booleans aren't set then they'll be zero which is FALSE so no project name or time stamp will be added.

As I think I mentioned previously this is part of the add in which can be downloaded from the codecage (thank you Hans) at ...
http://www.thecodecage.com/forumz/view. ... _downloads

Thank you for trying it out Rudi!! Great beta testing!!!

Hugs
Lisa

[Jan Karel Pieterse]

Code cleaner by Rob Bovey has such an option built-in...

[Rudi]

TX for the hint Jan Karel.