View Full Version : [SOLVED:] Bulk Export Worksheet Code
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
09-22-2016, 04:56 PM
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.
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
09-22-2016, 06:05 PM
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
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
09-22-2016, 06:42 PM
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
09-22-2016, 06:48 PM
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
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
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
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
Instead of:
Columns.Range("B1:CZ1").EntireColumn.Hidden = False
use
Range("B1:CZ1").EntireColumn.Hidden = False
or
columns(2).resize(,102).hidden=false
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.