Save All Worksheets as CSV
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
|