Exporting Worksheet Code Modules - Export Code As Worksheet Name

[dj44]

Hi folks,

Good Wednesday



Currently when I export my code stored in worksheets - I get

Sheet1
Sheet2
Sheet3

Can I export it as

Worksheet name

So I may have the code in the worksheets be exported but the bas file is named with the name of the worksheet it came from ie

DataWorksheet
Sales
Area
In stead of

Sheet1, 2 ,3 etc






Option Explicit
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3

Sub Export_Sheet_Code()


Dim oComponent As Object
Dim sPath As String

sPath = "C:\Users\DJ\Desktop\VBA\"

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" ' <<<<<<< what do i do here
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




I have researched all over but I cant seem to find the missing link

[snb]

Use .Codename instead of .Name

[dj44]

Hello snb,

I changed it to



Case vbext_ct_StdModule:
On Error Resume Next
Kill sPath & .CodeName & ".bas" '
On Error GoTo 0
.Export sPath & .CodeName & ".bas"


but now it says object not supported :(

[snb]

For Each sh In Sheets
ThisWorkbook.VBProject.VBComponents(sh.CodeName).Export "G:\OF\" & sh.Name & ".bas"
Next

or


For Each it In ThisWorkbook.VBProject.VBComponents
If it.Type = 100 Then it.Export "G:\OF\" & it.Properties("name") & ".bas"
Next

[dj44]

Thanks snb,

did the perfect job - I just wanted the sheet name so i know which code is in which sheet module.
But to no avail could i find this code



Have a good day - all and folks:)

[SamT]

VBComponent.Name = Sheet.CodeName ???

VBComponent.Object.Name = Sheet.Name ???

[snb]

@SamT


Sub M_snb()
For Each it In ThisWorkbook.VBProject.VBComponents
MsgBox it.Name ' = sheets(1...n).codename or workbooks(1...n).codename
MsgBox it.Object.Name ' not valid: errors out
MsgBox it.Properties("Name") ' = sheets(1 ... n).name or workbooks(1...n).name
Next
End Sub

[SamT]

Kool. Thanks.

I wonder why it.Name is not the same as it.Properties("Name")
Probably a question for Joel Spolsky.

Again, thanks.

[snb]

@SamT

Vbcomponents is a collection within the VBProject.
That collection has very distinct/different properties/methods than the collection 'sheets'.
Since you could say that 'VBcomponents' is a VBA-collection, the names are automatically 'codenames' (='VBA-names').