Excel VBA debugging help needed!

[mainemike]

I need help writing some VBA code. Here's what I'm trying to do...

When a user closes an Excel spreadsheet (results.xls), I want to use
Workbook/BeforeClose to export all the contents of all worksheets, as
CSVs, as tab name.csv, to the directory that the original spreadsheet
was opened from. I do not want any user interaction.

What I have so far is...

' Start of coding
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
Dim newWks As Worksheet
Dim MyPath As String
MyPath = ActiveWorkbook.Path
For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook
With ActiveSheet
..Parent.SaveAs Filename:=MyPath & "\" & .Name,
FileFormat:=xlCSV
..Parent.Close savechanges:=False
End With
Next wks
End Sub
' End of coding

Here are my problems so far...
1) It only saves one out of the four worksheets.
2) If the .csv file exists, it prompts to overwrite. I want it to
just write over without the prompt.
3) The user ends up with a new workbook that has only the first
worksheet. I'm assuming this is a result of the "wks.Copy" not
'cleaning up' correctly.


If anyone could help debug this, or give me suggestions on how to
streamline the code, I would greatly appreciate it!

 

[Dave Peterson]

It's not a problem with the wks.copy line.

It's a problem with the "with activesheet" line.

The activesheet is a property that can belong to a window, a workbook, or the
application.

Since this code is behind the ThisWorkbook module, and it's not qualified by
anything, excel/VBA assumes that it belongs to the thing holding the code--in
this case, it's the workbook that's closing.

Try this:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
Dim MyPath As String

MyPath = ActiveWorkbook.Path
For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook

With Application.ActiveSheet
Application.DisplayAlerts = False
.Parent.SaveAs Filename:=MyPath & "\" & .Name, _
FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
Next wks
End Sub

 

[Dave Peterson]

In fact, since this code is behind the ThisWorkbook module, I wouldn't use
Activeworkbook, either:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
Dim MyPath As String

MyPath = me.Path
For Each wks In me.Worksheets
wks.Copy 'copies to a new workbook

With Application.ActiveSheet
Application.DisplayAlerts = False
.Parent.SaveAs Filename:=MyPath & "\" & .Name, _
FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
Next wks
End Sub

Me in this case refers to the thing owning the code--ThisWorkbook.

 

[mainemike]

Dave,

Thanks for the input. I found some snippets on the web, and piecing
them together, I got...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
Dim newWks As Worksheet
Dim NFName As String
Dim varPath As String
Dim FName As String
Me.Saved = True
varPath = ThisWorkbook.Path
Application.DisplayAlerts = False
For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook
FName = wks.Name & ".csv"
NFName = varPath & "\" & FName
If WorkbookOpen(FName) Then
Workbooks(FName).Close SaveChanges:=False
End If
ActiveWorkbook.SaveAs Filename:=NFName, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
Next wks
Application.DisplayAlerts = True
End Sub

It seems bloated, but it works. Thanks for the help!

 

[Dave Peterson]

I'd try that other suggestion. It looks more straightforward to me.