Macro to save Macros

[davegb]

I googled here, but no joy. So, is it possible to create a macro to
save all my macros? I've accumulated quite a set by now, with a lot of
help here, and I've saved the modules. But it's getting tedious to save
them all. Has anyone done this?

[Jim Thomlinson]

You have lost me a bit here. When you save a spreadsheet all of the macros
are saved with it. If you have any modules, classes or forms that are
particularily useful and you would want to use them in other projects you can
export them, to be imported into the other porjects. With addin's you just
have to remember to save them when you make changes and before you exit excel
(or remove the addin). If this does not cover it let us know... Or maybe I am
just missing something...
--
HTH...

Jim Thomlinson


"davegb" wrote:

> I googled here, but no joy. So, is it possible to create a macro to
> save all my macros? I've accumulated quite a set by now, with a lot of
> help here, and I've saved the modules. But it's getting tedious to save
> them all. Has anyone done this?
>
>

[STEVE BELL]

This is from Chip Pearson's site -

http://www.cpearson.com/excel/vbe.htm



Exporting All Modules In A Project
The procedure below will list export all of the modules in a workbook to
text files. It will save the files in the same folder as the workbook. This
can be useful for saving a backup copy of your VBA, or for transferring VBA
code from one project to another.

Sub ExportAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim Sfx As String

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
End If
Next VBComp
End Sub



--
steveB

Remove "AYN" from email to respond
"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> You have lost me a bit here. When you save a spreadsheet all of the macros
> are saved with it. If you have any modules, classes or forms that are
> particularily useful and you would want to use them in other projects you
> can
> export them, to be imported into the other porjects. With addin's you just
> have to remember to save them when you make changes and before you exit
> excel
> (or remove the addin). If this does not cover it let us know... Or maybe I
> am
> just missing something...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "davegb" wrote:
>
>> I googled here, but no joy. So, is it possible to create a macro to
>> save all my macros? I've accumulated quite a set by now, with a lot of
>> help here, and I've saved the modules. But it's getting tedious to save
>> them all. Has anyone done this?
>>
>>

[Norman Jones]

Hi Dave,

Saving a workbook saves the the contained modules and, consequently, all
code held by the workbook.

Apart from periodic cleaning (using Rob Bovey's CodeCleaner Addin) you don't
need to do anything to the individual modules.

Macros that you want to be available to all workbooks can be stored in your
Personal.xls or in a workbook which you save and load as an addin.

If you adopt normal wise housekeeping, you will create regular backup copies
of your workbooks / addin the contained modules wll also be saved.

If you wish to easily produce a text file copy of all the modules in a
workbook, the CodeCleaner addin, mentioed above, provides a simple,
painless means of doing this.

---
Regards,
Norman



"davegb" <[email protected]> wrote in message
news:[email protected]...
>I googled here, but no joy. So, is it possible to create a macro to
> save all my macros? I've accumulated quite a set by now, with a lot of
> help here, and I've saved the modules. But it's getting tedious to save
> them all. Has anyone done this?
>

[davegb]

Thanks for all your replies!
Steve,
When I try to run your macro, I'm getting a "User defined type not
defined" error on the

Dim VBComp As VBIDE.VBComponent

line. I tracked it down on Chip's page, and found that it needs access
to a file in Winhelp which I can't access. Is there a workaround for
that?
Thanks!

STEVE BELL wrote:
> This is from Chip Pearson's site -
>
> http://www.cpearson.com/excel/vbe.htm
>
>
>
> Exporting All Modules In A Project
> The procedure below will list export all of the modules in a workbook to
> text files. It will save the files in the same folder as the workbook. This
> can be useful for saving a backup copy of your VBA, or for transferring VBA
> code from one project to another.
>
> Sub ExportAllVBA()
> Dim VBComp As VBIDE.VBComponent
> Dim Sfx As String
>
> For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> Select Case VBComp.Type
> Case vbext_ct_ClassModule, vbext_ct_Document
> Sfx = ".cls"
> Case vbext_ct_MSForm
> Sfx = ".frm"
> Case vbext_ct_StdModule
> Sfx = ".bas"
> Case Else
> Sfx = ""
> End Select
> If Sfx <> "" Then
> VBComp.Export _
> Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
> End If
> Next VBComp
> End Sub
>
>
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > You have lost me a bit here. When you save a spreadsheet all of the macros
> > are saved with it. If you have any modules, classes or forms that are
> > particularily useful and you would want to use them in other projects you
> > can
> > export them, to be imported into the other porjects. With addin's you just
> > have to remember to save them when you make changes and before you exit
> > excel
> > (or remove the addin). If this does not cover it let us know... Or maybe I
> > am
> > just missing something...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "davegb" wrote:
> >
> >> I googled here, but no joy. So, is it possible to create a macro to
> >> save all my macros? I've accumulated quite a set by now, with a lot of
> >> help here, and I've saved the modules. But it's getting tedious to save
> >> them all. Has anyone done this?
> >>
> >>

[Bob Phillips]

You need to set a reference to the Microsoft Visual Basic for Applications
Extensibility library, or just use

Dim VBComp As Object

--

HTH

RP
(remove nothere from the email address if mailing direct)


"davegb" <[email protected]> wrote in message
news:[email protected]...
> Thanks for all your replies!
> Steve,
> When I try to run your macro, I'm getting a "User defined type not
> defined" error on the
>
> Dim VBComp As VBIDE.VBComponent
>
> line. I tracked it down on Chip's page, and found that it needs access
> to a file in Winhelp which I can't access. Is there a workaround for
> that?
> Thanks!
>
> STEVE BELL wrote:
> > This is from Chip Pearson's site -
> >
> > http://www.cpearson.com/excel/vbe.htm
> >
> >
> >
> > Exporting All Modules In A Project
> > The procedure below will list export all of the modules in a workbook to
> > text files. It will save the files in the same folder as the workbook.
This
> > can be useful for saving a backup copy of your VBA, or for transferring
VBA
> > code from one project to another.
> >
> > Sub ExportAllVBA()
> > Dim VBComp As VBIDE.VBComponent
> > Dim Sfx As String
> >
> > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > Select Case VBComp.Type
> > Case vbext_ct_ClassModule, vbext_ct_Document
> > Sfx = ".cls"
> > Case vbext_ct_MSForm
> > Sfx = ".frm"
> > Case vbext_ct_StdModule
> > Sfx = ".bas"
> > Case Else
> > Sfx = ""
> > End Select
> > If Sfx <> "" Then
> > VBComp.Export _
> > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
> > End If
> > Next VBComp
> > End Sub
> >
> >
> >
> > --
> > steveB
> >
> > Remove "AYN" from email to respond
> > "Jim Thomlinson" <[email protected]> wrote in message
> > news:[email protected]...
> > > You have lost me a bit here. When you save a spreadsheet all of the
macros
> > > are saved with it. If you have any modules, classes or forms that are
> > > particularily useful and you would want to use them in other projects
you
> > > can
> > > export them, to be imported into the other porjects. With addin's you
just
> > > have to remember to save them when you make changes and before you
exit
> > > excel
> > > (or remove the addin). If this does not cover it let us know... Or
maybe I
> > > am
> > > just missing something...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "davegb" wrote:
> > >
> > >> I googled here, but no joy. So, is it possible to create a macro to
> > >> save all my macros? I've accumulated quite a set by now, with a lot
of
> > >> help here, and I've saved the modules. But it's getting tedious to
save
> > >> them all. Has anyone done this?
> > >>
> > >>
>

[davegb]

Modified the macro and declared VBComp as Object. Now I'm getting
"Variable not defined" on vbext_ct_ClassModule. I don't recognize this
kind of variable. How do I define it?
I don't know how to reference the extensibility library.
Thanks.

Bob Phillips wrote:
> You need to set a reference to the Microsoft Visual Basic for Applications
> Extensibility library, or just use
>
> Dim VBComp As Object
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for all your replies!
> > Steve,
> > When I try to run your macro, I'm getting a "User defined type not
> > defined" error on the
> >
> > Dim VBComp As VBIDE.VBComponent
> >
> > line. I tracked it down on Chip's page, and found that it needs access
> > to a file in Winhelp which I can't access. Is there a workaround for
> > that?
> > Thanks!
> >
> > STEVE BELL wrote:
> > > This is from Chip Pearson's site -
> > >
> > > http://www.cpearson.com/excel/vbe.htm
> > >
> > >
> > >
> > > Exporting All Modules In A Project
> > > The procedure below will list export all of the modules in a workbook to
> > > text files. It will save the files in the same folder as the workbook.
> This
> > > can be useful for saving a backup copy of your VBA, or for transferring
> VBA
> > > code from one project to another.
> > >
> > > Sub ExportAllVBA()
> > > Dim VBComp As VBIDE.VBComponent
> > > Dim Sfx As String
> > >
> > > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > > Select Case VBComp.Type
> > > Case vbext_ct_ClassModule, vbext_ct_Document
> > > Sfx = ".cls"
> > > Case vbext_ct_MSForm
> > > Sfx = ".frm"
> > > Case vbext_ct_StdModule
> > > Sfx = ".bas"
> > > Case Else
> > > Sfx = ""
> > > End Select
> > > If Sfx <> "" Then
> > > VBComp.Export _
> > > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
> > > End If
> > > Next VBComp
> > > End Sub
> > >
> > >
> > >
> > > --
> > > steveB
> > >
> > > Remove "AYN" from email to respond
> > > "Jim Thomlinson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > You have lost me a bit here. When you save a spreadsheet all of the
> macros
> > > > are saved with it. If you have any modules, classes or forms that are
> > > > particularily useful and you would want to use them in other projects
> you
> > > > can
> > > > export them, to be imported into the other porjects. With addin's you
> just
> > > > have to remember to save them when you make changes and before you
> exit
> > > > excel
> > > > (or remove the addin). If this does not cover it let us know... Or
> maybe I
> > > > am
> > > > just missing something...
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "davegb" wrote:
> > > >
> > > >> I googled here, but no joy. So, is it possible to create a macro to
> > > >> save all my macros? I've accumulated quite a set by now, with a lot
> of
> > > >> help here, and I've saved the modules. But it's getting tedious to
> save
> > > >> them all. Has anyone done this?
> > > >>
> > > >>
> >

[Bob Phillips]

Change that variable to 0 (zero)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"davegb" <[email protected]> wrote in message
news:[email protected]...
> Modified the macro and declared VBComp as Object. Now I'm getting
> "Variable not defined" on vbext_ct_ClassModule. I don't recognize this
> kind of variable. How do I define it?
> I don't know how to reference the extensibility library.
> Thanks.
>
> Bob Phillips wrote:
> > You need to set a reference to the Microsoft Visual Basic for
Applications
> > Extensibility library, or just use
> >
> > Dim VBComp As Object
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "davegb" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks for all your replies!
> > > Steve,
> > > When I try to run your macro, I'm getting a "User defined type not
> > > defined" error on the
> > >
> > > Dim VBComp As VBIDE.VBComponent
> > >
> > > line. I tracked it down on Chip's page, and found that it needs access
> > > to a file in Winhelp which I can't access. Is there a workaround for
> > > that?
> > > Thanks!
> > >
> > > STEVE BELL wrote:
> > > > This is from Chip Pearson's site -
> > > >
> > > > http://www.cpearson.com/excel/vbe.htm
> > > >
> > > >
> > > >
> > > > Exporting All Modules In A Project
> > > > The procedure below will list export all of the modules in a
workbook to
> > > > text files. It will save the files in the same folder as the
workbook.
> > This
> > > > can be useful for saving a backup copy of your VBA, or for
transferring
> > VBA
> > > > code from one project to another.
> > > >
> > > > Sub ExportAllVBA()
> > > > Dim VBComp As VBIDE.VBComponent
> > > > Dim Sfx As String
> > > >
> > > > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > > > Select Case VBComp.Type
> > > > Case vbext_ct_ClassModule, vbext_ct_Document
> > > > Sfx = ".cls"
> > > > Case vbext_ct_MSForm
> > > > Sfx = ".frm"
> > > > Case vbext_ct_StdModule
> > > > Sfx = ".bas"
> > > > Case Else
> > > > Sfx = ""
> > > > End Select
> > > > If Sfx <> "" Then
> > > > VBComp.Export _
> > > > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
> > > > End If
> > > > Next VBComp
> > > > End Sub
> > > >
> > > >
> > > >
> > > > --
> > > > steveB
> > > >
> > > > Remove "AYN" from email to respond
> > > > "Jim Thomlinson" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > You have lost me a bit here. When you save a spreadsheet all of
the
> > macros
> > > > > are saved with it. If you have any modules, classes or forms that
are
> > > > > particularily useful and you would want to use them in other
projects
> > you
> > > > > can
> > > > > export them, to be imported into the other porjects. With addin's
you
> > just
> > > > > have to remember to save them when you make changes and before you
> > exit
> > > > > excel
> > > > > (or remove the addin). If this does not cover it let us know... Or
> > maybe I
> > > > > am
> > > > > just missing something...
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "davegb" wrote:
> > > > >
> > > > >> I googled here, but no joy. So, is it possible to create a macro
to
> > > > >> save all my macros? I've accumulated quite a set by now, with a
lot
> > of
> > > > >> help here, and I've saved the modules. But it's getting tedious
to
> > save
> > > > >> them all. Has anyone done this?
> > > > >>
> > > > >>
> > >
>

[davegb]

Thanks, Bob
I set VBComp = 0, but it still doesn't recognize the user defined
variable. Any other ideas?

Bob Phillips wrote:
> Change that variable to 0 (zero)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> > Modified the macro and declared VBComp as Object. Now I'm getting
> > "Variable not defined" on vbext_ct_ClassModule. I don't recognize this
> > kind of variable. How do I define it?
> > I don't know how to reference the extensibility library.
> > Thanks.
> >
> > Bob Phillips wrote:
> > > You need to set a reference to the Microsoft Visual Basic for
> Applications
> > > Extensibility library, or just use
> > >
> > > Dim VBComp As Object
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "davegb" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Thanks for all your replies!
> > > > Steve,
> > > > When I try to run your macro, I'm getting a "User defined type not
> > > > defined" error on the
> > > >
> > > > Dim VBComp As VBIDE.VBComponent
> > > >
> > > > line. I tracked it down on Chip's page, and found that it needs access
> > > > to a file in Winhelp which I can't access. Is there a workaround for
> > > > that?
> > > > Thanks!
> > > >
> > > > STEVE BELL wrote:
> > > > > This is from Chip Pearson's site -
> > > > >
> > > > > http://www.cpearson.com/excel/vbe.htm
> > > > >
> > > > >
> > > > >
> > > > > Exporting All Modules In A Project
> > > > > The procedure below will list export all of the modules in a
> workbook to
> > > > > text files. It will save the files in the same folder as the
> workbook.
> > > This
> > > > > can be useful for saving a backup copy of your VBA, or for
> transferring
> > > VBA
> > > > > code from one project to another.
> > > > >
> > > > > Sub ExportAllVBA()
> > > > > Dim VBComp As VBIDE.VBComponent
> > > > > Dim Sfx As String
> > > > >
> > > > > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > > > > Select Case VBComp.Type
> > > > > Case vbext_ct_ClassModule, vbext_ct_Document
> > > > > Sfx = ".cls"
> > > > > Case vbext_ct_MSForm
> > > > > Sfx = ".frm"
> > > > > Case vbext_ct_StdModule
> > > > > Sfx = ".bas"
> > > > > Case Else
> > > > > Sfx = ""
> > > > > End Select
> > > > > If Sfx <> "" Then
> > > > > VBComp.Export _
> > > > > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
> > > > > End If
> > > > > Next VBComp
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > steveB
> > > > >
> > > > > Remove "AYN" from email to respond
> > > > > "Jim Thomlinson" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > You have lost me a bit here. When you save a spreadsheet all of
> the
> > > macros
> > > > > > are saved with it. If you have any modules, classes or forms that
> are
> > > > > > particularily useful and you would want to use them in other
> projects
> > > you
> > > > > > can
> > > > > > export them, to be imported into the other porjects. With addin's
> you
> > > just
> > > > > > have to remember to save them when you make changes and before you
> > > exit
> > > > > > excel
> > > > > > (or remove the addin). If this does not cover it let us know... Or
> > > maybe I
> > > > > > am
> > > > > > just missing something...
> > > > > > --
> > > > > > HTH...
> > > > > >
> > > > > > Jim Thomlinson
> > > > > >
> > > > > >
> > > > > > "davegb" wrote:
> > > > > >
> > > > > >> I googled here, but no joy. So, is it possible to create a macro
> to
> > > > > >> save all my macros? I've accumulated quite a set by now, with a
> lot
> > > of
> > > > > >> help here, and I've saved the modules. But it's getting tedious
> to
> > > save
> > > > > >> them all. Has anyone done this?
> > > > > >>
> > > > > >>
> > > >
> >

[Bob Phillips]

Sorry Dave, I was lazy for you. Try this

Sub ExportAllVBA()
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3
Const vbext_ct_Document As Long = 100
Dim VBComp As Object
Dim Sfx As String

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
End If
Next VBComp
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"davegb" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Bob
> I set VBComp = 0, but it still doesn't recognize the user defined
> variable. Any other ideas?
>
> Bob Phillips wrote:
> > Change that variable to 0 (zero)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "davegb" <[email protected]> wrote in message
> > news:[email protected]...
> > > Modified the macro and declared VBComp as Object. Now I'm getting
> > > "Variable not defined" on vbext_ct_ClassModule. I don't recognize this
> > > kind of variable. How do I define it?
> > > I don't know how to reference the extensibility library.
> > > Thanks.
> > >
> > > Bob Phillips wrote:
> > > > You need to set a reference to the Microsoft Visual Basic for
> > Applications
> > > > Extensibility library, or just use
> > > >
> > > > Dim VBComp As Object
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "davegb" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Thanks for all your replies!
> > > > > Steve,
> > > > > When I try to run your macro, I'm getting a "User defined type not
> > > > > defined" error on the
> > > > >
> > > > > Dim VBComp As VBIDE.VBComponent
> > > > >
> > > > > line. I tracked it down on Chip's page, and found that it needs
access
> > > > > to a file in Winhelp which I can't access. Is there a workaround
for
> > > > > that?
> > > > > Thanks!
> > > > >
> > > > > STEVE BELL wrote:
> > > > > > This is from Chip Pearson's site -
> > > > > >
> > > > > > http://www.cpearson.com/excel/vbe.htm
> > > > > >
> > > > > >
> > > > > >
> > > > > > Exporting All Modules In A Project
> > > > > > The procedure below will list export all of the modules in a
> > workbook to
> > > > > > text files. It will save the files in the same folder as the
> > workbook.
> > > > This
> > > > > > can be useful for saving a backup copy of your VBA, or for
> > transferring
> > > > VBA
> > > > > > code from one project to another.
> > > > > >
> > > > > > Sub ExportAllVBA()
> > > > > > Dim VBComp As VBIDE.VBComponent
> > > > > > Dim Sfx As String
> > > > > >
> > > > > > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > > > > > Select Case VBComp.Type
> > > > > > Case vbext_ct_ClassModule, vbext_ct_Document
> > > > > > Sfx = ".cls"
> > > > > > Case vbext_ct_MSForm
> > > > > > Sfx = ".frm"
> > > > > > Case vbext_ct_StdModule
> > > > > > Sfx = ".bas"
> > > > > > Case Else
> > > > > > Sfx = ""
> > > > > > End Select
> > > > > > If Sfx <> "" Then
> > > > > > VBComp.Export _
> > > > > > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name &
Sfx
> > > > > > End If
> > > > > > Next VBComp
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > steveB
> > > > > >
> > > > > > Remove "AYN" from email to respond
> > > > > > "Jim Thomlinson" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > You have lost me a bit here. When you save a spreadsheet all
of
> > the
> > > > macros
> > > > > > > are saved with it. If you have any modules, classes or forms
that
> > are
> > > > > > > particularily useful and you would want to use them in other
> > projects
> > > > you
> > > > > > > can
> > > > > > > export them, to be imported into the other porjects. With
addin's
> > you
> > > > just
> > > > > > > have to remember to save them when you make changes and before
you
> > > > exit
> > > > > > > excel
> > > > > > > (or remove the addin). If this does not cover it let us
know... Or
> > > > maybe I
> > > > > > > am
> > > > > > > just missing something...
> > > > > > > --
> > > > > > > HTH...
> > > > > > >
> > > > > > > Jim Thomlinson
> > > > > > >
> > > > > > >
> > > > > > > "davegb" wrote:
> > > > > > >
> > > > > > >> I googled here, but no joy. So, is it possible to create a
macro
> > to
> > > > > > >> save all my macros? I've accumulated quite a set by now, with
a
> > lot
> > > > of
> > > > > > >> help here, and I've saved the modules. But it's getting
tedious
> > to
> > > > save
> > > > > > >> them all. Has anyone done this?
> > > > > > >>
> > > > > > >>
> > > > >
> > >
>

[davegb]

Bob, thanks a lot. Works great now!

Bob Phillips wrote:
> Sorry Dave, I was lazy for you. Try this
>
> Sub ExportAllVBA()
> Const vbext_ct_StdModule As Long = 1
> Const vbext_ct_ClassModule As Long = 2
> Const vbext_ct_MSForm As Long = 3
> Const vbext_ct_Document As Long = 100
> Dim VBComp As Object
> Dim Sfx As String
>
> For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> Select Case VBComp.Type
> Case vbext_ct_ClassModule, vbext_ct_Document
> Sfx = ".cls"
> Case vbext_ct_MSForm
> Sfx = ".frm"
> Case vbext_ct_StdModule
> Sfx = ".bas"
> Case Else
> Sfx = ""
> End Select
> If Sfx <> "" Then
> VBComp.Export _
> Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
> End If
> Next VBComp
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks, Bob
> > I set VBComp = 0, but it still doesn't recognize the user defined
> > variable. Any other ideas?
> >
> > Bob Phillips wrote:
> > > Change that variable to 0 (zero)
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "davegb" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Modified the macro and declared VBComp as Object. Now I'm getting
> > > > "Variable not defined" on vbext_ct_ClassModule. I don't recognize this
> > > > kind of variable. How do I define it?
> > > > I don't know how to reference the extensibility library.
> > > > Thanks.
> > > >
> > > > Bob Phillips wrote:
> > > > > You need to set a reference to the Microsoft Visual Basic for
> > > Applications
> > > > > Extensibility library, or just use
> > > > >
> > > > > Dim VBComp As Object
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "davegb" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Thanks for all your replies!
> > > > > > Steve,
> > > > > > When I try to run your macro, I'm getting a "User defined type not
> > > > > > defined" error on the
> > > > > >
> > > > > > Dim VBComp As VBIDE.VBComponent
> > > > > >
> > > > > > line. I tracked it down on Chip's page, and found that it needs
> access
> > > > > > to a file in Winhelp which I can't access. Is there a workaround
> for
> > > > > > that?
> > > > > > Thanks!
> > > > > >
> > > > > > STEVE BELL wrote:
> > > > > > > This is from Chip Pearson's site -
> > > > > > >
> > > > > > > http://www.cpearson.com/excel/vbe.htm
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Exporting All Modules In A Project
> > > > > > > The procedure below will list export all of the modules in a
> > > workbook to
> > > > > > > text files. It will save the files in the same folder as the
> > > workbook.
> > > > > This
> > > > > > > can be useful for saving a backup copy of your VBA, or for
> > > transferring
> > > > > VBA
> > > > > > > code from one project to another.
> > > > > > >
> > > > > > > Sub ExportAllVBA()
> > > > > > > Dim VBComp As VBIDE.VBComponent
> > > > > > > Dim Sfx As String
> > > > > > >
> > > > > > > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > > > > > > Select Case VBComp.Type
> > > > > > > Case vbext_ct_ClassModule, vbext_ct_Document
> > > > > > > Sfx = ".cls"
> > > > > > > Case vbext_ct_MSForm
> > > > > > > Sfx = ".frm"
> > > > > > > Case vbext_ct_StdModule
> > > > > > > Sfx = ".bas"
> > > > > > > Case Else
> > > > > > > Sfx = ""
> > > > > > > End Select
> > > > > > > If Sfx <> "" Then
> > > > > > > VBComp.Export _
> > > > > > > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name &
> Sfx
> > > > > > > End If
> > > > > > > Next VBComp
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > steveB
> > > > > > >
> > > > > > > Remove "AYN" from email to respond
> > > > > > > "Jim Thomlinson" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > You have lost me a bit here. When you save a spreadsheet all
> of
> > > the
> > > > > macros
> > > > > > > > are saved with it. If you have any modules, classes or forms
> that
> > > are
> > > > > > > > particularily useful and you would want to use them in other
> > > projects
> > > > > you
> > > > > > > > can
> > > > > > > > export them, to be imported into the other porjects. With
> addin's
> > > you
> > > > > just
> > > > > > > > have to remember to save them when you make changes and before
> you
> > > > > exit
> > > > > > > > excel
> > > > > > > > (or remove the addin). If this does not cover it let us
> know... Or
> > > > > maybe I
> > > > > > > > am
> > > > > > > > just missing something...
> > > > > > > > --
> > > > > > > > HTH...
> > > > > > > >
> > > > > > > > Jim Thomlinson
> > > > > > > >
> > > > > > > >
> > > > > > > > "davegb" wrote:
> > > > > > > >
> > > > > > > >> I googled here, but no joy. So, is it possible to create a
> macro
> > > to
> > > > > > > >> save all my macros? I've accumulated quite a set by now, with
> a
> > > lot
> > > > > of
> > > > > > > >> help here, and I've saved the modules. But it's getting
> tedious
> > > to
> > > > > save
> > > > > > > >> them all. Has anyone done this?
> > > > > > > >>
> > > > > > > >>
> > > > > >
> > > >
> >

[Bob Phillips]

:-) I had forgotten this one.

Bob


"davegb" <[email protected]> wrote in message
news:[email protected]...
> Bob, thanks a lot. Works great now!
>
> Bob Phillips wrote:
> > Sorry Dave, I was lazy for you. Try this
> >
> > Sub ExportAllVBA()
> > Const vbext_ct_StdModule As Long = 1
> > Const vbext_ct_ClassModule As Long = 2
> > Const vbext_ct_MSForm As Long = 3
> > Const vbext_ct_Document As Long = 100
> > Dim VBComp As Object
> > Dim Sfx As String
> >
> > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > Select Case VBComp.Type
> > Case vbext_ct_ClassModule, vbext_ct_Document
> > Sfx = ".cls"
> > Case vbext_ct_MSForm
> > Sfx = ".frm"
> > Case vbext_ct_StdModule
> > Sfx = ".bas"
> > Case Else
> > Sfx = ""
> > End Select
> > If Sfx <> "" Then
> > VBComp.Export _
> > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
> > End If
> > Next VBComp
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "davegb" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks, Bob
> > > I set VBComp = 0, but it still doesn't recognize the user defined
> > > variable. Any other ideas?
> > >
> > > Bob Phillips wrote:
> > > > Change that variable to 0 (zero)
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "davegb" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Modified the macro and declared VBComp as Object. Now I'm getting
> > > > > "Variable not defined" on vbext_ct_ClassModule. I don't recognize
this
> > > > > kind of variable. How do I define it?
> > > > > I don't know how to reference the extensibility library.
> > > > > Thanks.
> > > > >
> > > > > Bob Phillips wrote:
> > > > > > You need to set a reference to the Microsoft Visual Basic for
> > > > Applications
> > > > > > Extensibility library, or just use
> > > > > >
> > > > > > Dim VBComp As Object
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "davegb" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Thanks for all your replies!
> > > > > > > Steve,
> > > > > > > When I try to run your macro, I'm getting a "User defined type
not
> > > > > > > defined" error on the
> > > > > > >
> > > > > > > Dim VBComp As VBIDE.VBComponent
> > > > > > >
> > > > > > > line. I tracked it down on Chip's page, and found that it
needs
> > access
> > > > > > > to a file in Winhelp which I can't access. Is there a
workaround
> > for
> > > > > > > that?
> > > > > > > Thanks!
> > > > > > >
> > > > > > > STEVE BELL wrote:
> > > > > > > > This is from Chip Pearson's site -
> > > > > > > >
> > > > > > > > http://www.cpearson.com/excel/vbe.htm
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Exporting All Modules In A Project
> > > > > > > > The procedure below will list export all of the modules in a
> > > > workbook to
> > > > > > > > text files. It will save the files in the same folder as the
> > > > workbook.
> > > > > > This
> > > > > > > > can be useful for saving a backup copy of your VBA, or for
> > > > transferring
> > > > > > VBA
> > > > > > > > code from one project to another.
> > > > > > > >
> > > > > > > > Sub ExportAllVBA()
> > > > > > > > Dim VBComp As VBIDE.VBComponent
> > > > > > > > Dim Sfx As String
> > > > > > > >
> > > > > > > > For Each VBComp In ActiveWorkbook.VBProject.VBComponents
> > > > > > > > Select Case VBComp.Type
> > > > > > > > Case vbext_ct_ClassModule, vbext_ct_Document
> > > > > > > > Sfx = ".cls"
> > > > > > > > Case vbext_ct_MSForm
> > > > > > > > Sfx = ".frm"
> > > > > > > > Case vbext_ct_StdModule
> > > > > > > > Sfx = ".bas"
> > > > > > > > Case Else
> > > > > > > > Sfx = ""
> > > > > > > > End Select
> > > > > > > > If Sfx <> "" Then
> > > > > > > > VBComp.Export _
> > > > > > > > Filename:=ActiveWorkbook.Path & "\" & VBComp.Name
&
> > Sfx
> > > > > > > > End If
> > > > > > > > Next VBComp
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > steveB
> > > > > > > >
> > > > > > > > Remove "AYN" from email to respond
> > > > > > > > "Jim Thomlinson" <[email protected]> wrote in
message
> > > > > > > > news:[email protected]...
> > > > > > > > > You have lost me a bit here. When you save a spreadsheet
all
> > of
> > > > the
> > > > > > macros
> > > > > > > > > are saved with it. If you have any modules, classes or
forms
> > that
> > > > are
> > > > > > > > > particularily useful and you would want to use them in
other
> > > > projects
> > > > > > you
> > > > > > > > > can
> > > > > > > > > export them, to be imported into the other porjects. With
> > addin's
> > > > you
> > > > > > just
> > > > > > > > > have to remember to save them when you make changes and
before
> > you
> > > > > > exit
> > > > > > > > > excel
> > > > > > > > > (or remove the addin). If this does not cover it let us
> > know... Or
> > > > > > maybe I
> > > > > > > > > am
> > > > > > > > > just missing something...
> > > > > > > > > --
> > > > > > > > > HTH...
> > > > > > > > >
> > > > > > > > > Jim Thomlinson
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "davegb" wrote:
> > > > > > > > >
> > > > > > > > >> I googled here, but no joy. So, is it possible to create
a
> > macro
> > > > to
> > > > > > > > >> save all my macros? I've accumulated quite a set by now,
with
> > a
> > > > lot
> > > > > > of
> > > > > > > > >> help here, and I've saved the modules. But it's getting
> > tedious
> > > > to
> > > > > > save
> > > > > > > > >> them all. Has anyone done this?
> > > > > > > > >>
> > > > > > > > >>
> > > > > > >
> > > > >
> > >
>