Bulk Export Worksheet Code

[dj44]

hi folks,

good thursday all.:)


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


17140


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 :doh:

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

17142

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 (

17143




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.:doh:

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 :grinhalo: from my self at least!

Have a great evening

:beerchug:
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 :grinhalo:

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