dj44
01-11-2017, 07:36 AM
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
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