Save to CSV macro crashes Excel 2003

  • Thread starter Thread starter Scott Bass
  • Start date Start date
S

Scott Bass

Hi,

Problem 0:

First of all, I'm not an Excel macro or VB programmer...

Problem 1:

Here is my design criteria:

* A workbook with 1 to n worksheets
* When the user saves the workbook, cycle thru all worksheets, saving them
to a CSV file in the same directory as the workbook
* Ideally, it would be great if I could detect whether the worksheet had
been modified and actually needed saving. I assume this would greatly
improve performance if I only changed one worksheet out of say 20.
* But it's critical that the CSV's be in sync with the worksheets, so
unconditionally saving of all worksheets is acceptable
* Then save the workbook itself in XLS format
* Return the user to the exact worksheet and cell he/she was editing before
the save
* I want the macro to override the normal save functionality (i.e. run if
the user clicks the save icon), as opposed to linking it to a specific
button added to the workbook.

From Googling the net plus some help from a colleague, here are my first two
attempts:

Version 1:

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

On Error Resume Next

ThisBook = ThisWorkbook.Name
ThisPath = ThisWorkbook.Path

Application.DisplayAlerts = False
Application.EnableEvents = False

For Each Sheet In ThisWorkbook.Sheets
Sheet.Activate
ActiveSheet.SaveAs Filename:=ThisPath & "\" & ActiveSheet.Name,
FileFormat:=xlCSV _
, CreateBackup:=False
Next

ThisWorkbook.Sheets(1).Activate
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ThisBook,
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=True

ThisWorkbook.Saved = True

Application.DisplayAlerts = True
Application.EnableEvents = True

Exit Sub

End Sub

Version 2:

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

If SaveAsUI Then GoTo ExitHere

ThisBook = ThisWorkbook.Name
ThisPath = ThisWorkbook.Path

Application.DisplayAlerts = False
Application.EnableEvents = False

For Each Sheet In ThisWorkbook.Sheets
Sheet.Activate
ActiveSheet.SaveAs Filename:=ThisPath & "\" & ActiveSheet.Name,
FileFormat:=xlCSV _
, CreateBackup:=False
Next

ThisWorkbook.Sheets(1).Activate
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ThisBook,
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

ThisWorkbook.Saved = True

Application.DisplayAlerts = True
Application.EnableEvents = True


ExitHere:
Exit Sub

End Sub

Neither of these totally meet the design criteria stated above, eg.
returning to the worksheet and cell the user was previously editing. Any
assistance meeting the above design criteria appreciated.

Problem 2:
Both of these macros crash Excel on my work machine, but not my home
machine. It also crashes Excel 2007 as tested by our helpdesk on another
machine. The Excel patch level on both my home and work machines is Excel
2003 (11.8211.8202) SP3.

There are of course differences between my work and home machine, but not
too onerous. I'm an administrator on my work machine, but there will be
some security settings that are different.

I know this is a tough question, but are there any known issues that would
cause the above macro to ***crash Excel*** (therefore it's a bug in Excel,
full stop)??? Or known issues that would cause Excel macros to crash in
general? Or, if you can be bothered to run this, does it crash on your
machine as well?

Thanks for any help you can offer.

Regards,
Scott
 
Hi,

OK, forget about solving the crashing in Excel. But if you can assist with
the below design criteria that would be fantastic.

Regards,
Scott
 
* Ideally, it would be great if I could detect whether the worksheet had
been modified and actually needed saving.
Click to expand...
I didn't do anything with this but it can (and should) be done.
* Then save the workbook itself in XLS format
Click to expand...
Since you're using the beforesave workbook event, the workbook will be
saved in it's present format after the procedure runs.
* Return the user to the exact worksheet and cell he/she was editing before
the save
Click to expand...
The active sheet/cell won't change, so no return is necessary.

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

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

ThisPath = Path 'same here

FileName = ThisPath & "\" & Sheet.Name & ".csv"

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

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

Exit Sub

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

Cliff Edwards
 
Change:

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

to

Cleanup:
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = true
End With
 
Back
Top