Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save All Worksheets as CSV
#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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save All Worksheets as CSV
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save All Worksheets as CSV
#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: <<snipped 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save All Worksheets as CSV
On Nov 10, 8:53*am, Dave Peterson wrote:
#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: <<snipped 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save All Worksheets as CSV
On Nov 10, 8:53*am, Dave Peterson wrote:
#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: <<snipped 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save All Worksheets as CSV
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: On Nov 10, 8:53 am, Dave wrote: #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: <<snipped 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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save All Worksheets as CSV
On Nov 12, 10:54*pm, Dave Peterson wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do certain worksheets ALWAYS ask if I want to save them when closing them? | Excel Discussion (Misc queries) | |||
Save several worksheets into one file | Excel Programming | |||
I have a workbook of 12 worksheets. I want to only save one. how? | Excel Discussion (Misc queries) | |||
save worksheets within workbook | Excel Programming | |||
save button in excel to save one of the worksheets with a cell value as its name | Excel Programming |