On Nov 9, 11:54*pm, Dave Peterson wrote:
#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.htmh....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 both xl2003
and xl2007.http://msmvps.com/blogs/xldynamic/ar...ploy-me-simple...
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
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