Save All Worksheets as CSV

[Scott Bass]

Hi,

I've got this macro (thanks to this newsgroup), which saves all
worksheets as separate CSV files:

Option Explicit

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)

On Error GoTo errHandler

Dim ThisPath As String
Dim Sheet As Worksheet
Dim FileName As String

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

'Since you're in the workbook module, no workbook reference is
required when referring to this workbook
For Each Sheet In Sheets

ThisPath = Path 'same here
FileName = ThisPath & "\" & Sheet.Name & ".csv"

Sheet.Copy
With ActiveWorkbook
.SaveAs FileName:=FileName, FileFormat:=xlCSV
.Close 'I took the liberty of closing the newly created csv
files
End With
Next

Cleanup:
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Exit Sub

errHandler:
MsgBox Err.Source & " " & _
Err.Number & " " & _
Err.Description
GoTo Cleanup
End Sub


Two issues:

1) This works OK, but unconditionally saves each worksheet. Can I
add logic to only save worksheets that have been modified?

2) This works OK if this macro is an object of the desired workbook.
I then override the Workbook_BeforeSave event, and whenever I save the
workbook, all worksheets are saved as CSVs in the same path as the
open workbook.

However, I would prefer to store this macro as an object of my XLSTART
workbook, so that it is available to all workbooks. If I want to
automatically call the macro, I override the Workbook_BeforeSave event
as before. Otherwise, I manually invoke the macro via Alt-F8.

I can't figure out how to modify this macro in this scenario. I
*think* it would be something like passing the current workbook as an
object parameter to this macro, then modifying the macro calling
methods of that object.

Any help greatly appreciated.

Regards,
Scott

 

[Dave Peterson]

#1. I think you'll find that excel doesn't keep track of which sheets have been
modified. So if you wanted to keep track of that info yourself, you could run
the macro against just the sheets you want -- or maybe just run it against the
activesheet (as often as you want).

#2. I would drop the code from the _BeforeSave event and replace it with a
dedicated macro in a workbook in my XLStart folder (possibly personal.xl*).

This is the macro I'd use:

Option Explicit
Sub testme()

On Error GoTo errHandler:

Dim wks As Worksheet 'sheet isn't a good variable name
Dim myFileName As String

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With ActiveWorkbook
If .Path = "" Then
'it hasn't been saved
MsgBox "Please save the workbook normally and try again"
GoTo Cleanup:
End If

For Each wks In .Worksheets
myFileName = .Path & "\" & wks.Name & ".csv"

wks.Copy 'to a new workbook
With ActiveWorkbook
.SaveAs FileName:=myFileName, FileFormat:=xlCSV
.Close savechanges:=False
End With
Next wks
End With

Cleanup:
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Exit Sub

errHandler:
MsgBox Err.Source & " " & _
Err.Number & " " & _
Err.Description
GoTo Cleanup
End Sub

The next thing I'd want to do is to give the user a way to run that dedicated macro.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003
and xl2007.
http://msmvps.com/blogs/xldynamic/archive/2010/03/27/deploy-me-simple.aspx

 

[Scott Bass]

#1.  I think you'll find that excel doesn't keep track of which sheets have been
modified.  So if you wanted to keep track of that info yourself, you could run
the macro against just the sheets you want -- or maybe just run it against the
activesheet (as often as you want).

#2.  I would drop the code from the _BeforeSave event and replace it with a
dedicated macro in a workbook in my XLStart folder (possibly personal.xl*).

This is the macro I'd use:

Option Explicit
Sub testme()

     On Error GoTo errHandler:

     Dim wks As Worksheet 'sheet isn't a good variable name
     Dim myFileName As String

     With Application
         .DisplayAlerts = False
         .EnableEvents = False
         .ScreenUpdating = False
     End With

     With ActiveWorkbook
         If .Path = "" Then
             'it hasn't been saved
             MsgBox "Please save the workbook normally and try again"
             GoTo Cleanup:
         End If

         For Each wks In .Worksheets
             myFileName = .Path & "\" & wks.Name & ".csv"

             wks.Copy 'to a new workbook
             With ActiveWorkbook
                 .SaveAs FileName:=myFileName, FileFormat:=xlCSV
                 .Close savechanges:=False
             End With
         Next wks
     End With

Cleanup:
     With Application
         .DisplayAlerts = True
         .EnableEvents = True
         .ScreenUpdating = True
     End With

     Exit Sub

errHandler:
         MsgBox Err.Source & " " & _
         Err.Number & " " & _
         Err.Description
         GoTo Cleanup
End Sub

The next thing I'd want to do is to give the user a way to run that dedicated macro.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:http://www.rondebruin.nl/ribbon.htmhttp://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
orhttp://www.rondebruin.nl/2007addin.htm

And Bob Phillips shows a way to use a wrapper so that it can work in bothxl2003
and xl2007.http://msmvps.com/blogs/xldynamic/archive/2010/03/27/deploy-me-simple...

Hi Dave,

What I want to do is set this up for *very* non-technical users. In a
word description:

1. "Copy this macro to your XLSTART\personal.xls file".

2. "If you want this macro to run automatically when you save the
file, copy this (one-liner) code to your current workbook". (This
would just be a short "one-liner" call to the main macro".

3. "Otherwise, if you want to manually execute this macro, press Alt-
F8 then select the macro".

I assume #1 is covered by your code above. I don't know how to code
#2. Most of the time, auto-executing the macro whenever the workbook
is saved is the desired approach.

Thanks for the help...

Scott

 

[Dave Peterson]

#1. If you're using xl2003 or below (or can live with the menus under the addin
group of the ribbon in xl2007+), then use the link that points at Debra
Dalgleish's site to create a separate workbook that contains this macro and
creates the toolbar.

If you're using xl2007, you can use the code at Ron de Bruin's site to modify
the ribbon.

After you have that created, you can tell the user to store that file (DO NOT
NAME IT PERSONAL.*) in their XLStart folder.

#2. See #1.

#3. See #1.

I wouldn't expect most users to manage the macro by copying it into their
personal.xl* workbook.

On 11/09/2010 14:25, Scott Bass wrote:

 

[Scott Bass]

#1.  If you're using xl2003 or below (or can live with the menus under the addin
group of the ribbon in xl2007+), then use the link that points at Debra
Dalgleish's site to create a separate workbook that contains this macro and
creates the toolbar.

If you're using xl2007, you can use the code at Ron de Bruin's site to modify
the ribbon.

After you have that created, you can tell the user to store that file (DONOT
NAME IT PERSONAL.*) in their XLStart folder.

#2.  See #1.

#3.  See #1.

I wouldn't expect most users to manage the macro by copying it into their
personal.xl* workbook.

On 11/09/2010 14:25, Scott Bass wrote:
<<snipped>>

I've got the macro working if I 1) create it in my autostart workbook,
and 2) call it with Alt-F8.

However, if I want to call it automatically whenever I save the
workbook, I can't get it to work.

I've tried:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
SaveAllAsCSV2
End Sub

and

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
SaveAllAsCSV2()
End Sub

and

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call SaveAllAsCSV2
End Sub

but they all end in

Sub or Function not found.

How can I bind the autostart subroutine to the Workbook_BeforeSave
event?

Thanks,
Scott

 

[Scott Bass]

#1.  If you're using xl2003 or below (or can live with the menus under the addin
group of the ribbon in xl2007+), then use the link that points at Debra
Dalgleish's site to create a separate workbook that contains this macro and
creates the toolbar.

If you're using xl2007, you can use the code at Ron de Bruin's site to modify
the ribbon.

After you have that created, you can tell the user to store that file (DONOT
NAME IT PERSONAL.*) in their XLStart folder.

#2.  See #1.

#3.  See #1.

I wouldn't expect most users to manage the macro by copying it into their
personal.xl* workbook.

On 11/09/2010 14:25, Scott Bass wrote:
<<snipped>>

I've got the macro working if I 1) create it in my autostart workbook,
and 2) call it with Alt-F8.

However, if I want to call it automatically whenever I save the
workbook, I can't get it to work.

I've tried:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
SaveAllAsCSV2
End Sub

and

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
SaveAllAsCSV2()
End Sub

and

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call SaveAllAsCSV2
End Sub

but they all end in

Sub or Function not found.

How can I bind the autostart subroutine to the Workbook_BeforeSave
event?

Thanks,
Scott

 

[Dave Peterson]

Again, I wouldn't use the workbook events.

I'd create a new, dedicated macro workbook that would do the work.

 

[Scott Bass]

Again, I wouldn't use the workbook events.

I'd create a new, dedicated macro workbook that would do the work.

On 11/12/2010 04:39, Scott Bass wrote:

Again, I want to use the workbook events, specifically the
Workbook_BeforeSave event, so that anytime the workbook is saved, the
CSV files are created.

Anyone else? How can I call a macro in the autostart workbook from
another workbook's Workbook_BeforeSave event?

Thanks,
Scott

P.S.: Thanks Dave for your previous help. Much appreciated.