#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/ar...me-simple.aspx

On 11/09/2010 00:23, Scott Bass wrote:
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