Bulk Export Worksheet Code

[dj44]

hi folks,

good thursday all.


Is there a way for me to bulk export my worksheet code.


Code.png


Each of my worksheet has code in it, and i would like to have a copy of it.

just in case i lose it, as i always do

I just wanted to save each worksheet code as a text file or anything really.

I copied and pasted the first 15 but i got 20 excel books and 20 plus sheets each and well, it will take me a long time copy pasting and renmaing.

I've been round everywhere but I cant seem to find anything.

I got plently for exporting modules - but nothing for the private worksheet code.


any ideas

thank you

[Paul_Hossler]

http://www.appspro.com/Utilities/CodeCleaner.htm

Code Cleaner Features

• COM Add-In - The VBA Code Cleaner is now implemented as a COM add-in. This significantly improves the speed and stability of the program.
• Export Code Files - This feature allows you to export all the code files from a project to the directory you specify without the need to perform a project cleaning.
• Import Code Files - This feature allows you to import all code files from a specified directory into the project you select.
• Automation - All features of the code cleaner are exposed to VBA through COM automation so you can create your own applications using the code cleaner as a component

READ the installation instructions

Before installing the Code Cleaner you must set your security to trust access to Visual Basic projects.

• Excel 2010 and Later - Choose File > Options > Trust Center > Trust Center Settings > Macro Settings. Check the checkbox labeled Trust Access to Visual Basic Project and click OK twice to finish.

[dj44]

Hello Paul,

what a great tool.

NOTE: The VBA Code Cleaner DOES NOT support 64-bit versions of Excel. It runs in all 32-bit versions of Excel on any version of Windows.

Would it still work, i know that sounds daft, but, anything to beat copy and pasting,

I have 64

[Paul_Hossler]

Well, the UI could be improved, but try this

Option Explicit
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3
 
Sub ExportModules()
    Dim oComponent As Object
    Dim sPath As String
     
    sPath = "L:\Exports\"
     
    For Each oComponent In ActiveWorkbook.VBProject.VBComponents
        With oComponent
         
        Select Case .Type
            Case vbext_ct_StdModule:
                On Error Resume Next
                Kill sPath & .Name & ".bas"
                On Error GoTo 0
                .Export sPath & .Name & ".bas"
            Case vbext_ct_MSForm:
                On Error Resume Next
                Kill sPath & .Name & ".frm"
                Kill sPath & .Name & ".frx"
                On Error GoTo 0
                .Export sPath & .Name & ".frm"
            Case vbext_ct_ClassModule:
                
                On Error Resume Next
                Kill sPath & .Name & ".cls"
                On Error GoTo 0
                .Export sPath & .Name & ".cls"
        
        End Select
    
        End With
    Next
End Sub

[dj44]

Hi Paul,

thanks for the code to export the codes.

Inside each worksheet - i have stuff like this

worksheet code.jpg

This is what I am trying to get out as ive hidden my code gems in there for safe keeping

I'm sorry if i wan't good at explaining this, but is it possible to get this out?

I know you can right click and export each sheet as a .cls which i just discovered.

Case vbext_ct_ClassModule:
                 
                On Error Resume Next
                Kill sPath & .Name & ".cls"
                On Error GoTo 0
                .Export sPath & .Name & ".cls"

should the above snippet export the worksheet as a .cls?

[Paul_Hossler]

Just add the vbext_ct_Document to the first Case to export it

I didn't experiment with importing it

Option Explicit
 
Sub ExportModules()
    Dim oComponent As Object
    Dim sPath As String
     
    sPath = "L:\Exports\"
     
    For Each oComponent In ActiveWorkbook.VBProject.VBComponents
        With oComponent
            Select Case .Type
                Case vbext_ct_StdModule, vbext_ct_Document:
                    On Error Resume Next
                    Kill sPath & .Name & ".bas"
                    On Error GoTo 0
                    .Export sPath & .Name & ".bas"
                
                Case vbext_ct_MSForm:
                    On Error Resume Next
                    Kill sPath & .Name & ".frm"
                    Kill sPath & .Name & ".frx"
                    On Error GoTo 0
                    .Export sPath & .Name & ".frm"
                
                Case vbext_ct_ClassModule:
                    On Error Resume Next
                    Kill sPath & .Name & ".cls"
                    On Error GoTo 0
                    .Export sPath & .Name & ".cls"
            End Select
             
        End With
    Next
End Sub

[Paul_Hossler]

should the above snippet export the worksheet as a .cls?

There are 5 different 'values' for different modules, so I think the vbext_ct_document would be a .bas (

Capture.JPG




Chip has a good write up

http://www.cpearson.com/Excel/VBE.aspx

[dj44]

Paul,

thank you for being such a valuable and generous gentleman with your green code hands.

I've been looking for this for 3 days on google, dont tell anyone.

I did come accross Mr Chips stuff, but i couldnt find the worksheet bit.

I was becoming a bit anxious over my code in the worksheets,
as you never know when the excel workbook will become corrupted, and I cant stand to lose my code.

Because it takes me ages to work out how to do something and then ask people to help make it for me so i can use it, and the modules always get misplaced - becuase i have to rename them when i use them.

I will create a shortcut button on my excel book so i can run it for all of them and export the worksheet code.

Paul again thanks ever so much - My code will be safe now from my self at least!

Have a great evening


and happy early weekend all

[snb]

You could simplify the code you showed to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A2,A4,A6,A8,A10,A12")) Is Nothing Then Columns(3).Resize(, 20 + 26 * (Target.Row \ 2 - 1)).Hidden = True
End Sub

For the exporting of VBA-code see:

http://www.snb-vba.eu/VBA_Excel_VBproject_en.html

[dj44]

Hello SNB,

what a great trick!

To be honest i dont' like lots of code, but i dont know how else to do it

So this will save me lots of code in my private worksheet.

i added a back to home with

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

     Select Case Target.Address

     Case "$A$1"
     Columns.Range("B1:CZ1").EntireColumn.Hidden = False
     
     End Select

     If Not Intersect(Target, Range("A2,A4,A6,A8,A10,A12")) Is Nothing Then
     Columns(3).Resize(, 20 + 26 * (Target.Row \ 2 - 1)).Hidden = True
     End If
    
    
End Sub

As you can see my private worksheet was getting cluttered, time for a clean up now, and then another export

Have a good evening friends

[snb]

Instead of:

Columns.Range("B1:CZ1").EntireColumn.Hidden = False

use

Range("B1:CZ1").EntireColumn.Hidden = False

or

columns(2).resize(,102).hidden=false

[dj44]

thank you snb