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!


--
mainemike

[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


mainemike wrote:
>
> 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!
>
> --
> mainemike

--

Dave Peterson

[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.

Dave Peterson wrote:
>
> 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
>
> mainemike wrote:
> >
> > 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!
> >
> > --
> > mainemike
>
> --
>
> Dave Peterson

--

Dave Peterson

[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 Wrote:
> 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.
>
> Dave Peterson wrote:
>
> 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
>
> mainemike wrote:
>
> 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!
>
> --
> mainemike
>
> --
>
> Dave Peterson
>
> --
>
> Dave Peterson


--
mainemike

[Dave Peterson]

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

mainemike wrote:
>
> 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 Wrote:
> > 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.
> >
> > Dave Peterson wrote:
> >
> > 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
> >
> > mainemike wrote:
> >
> > 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!
> >
> > --
> > mainemike
> >
> > --
> >
> > Dave Peterson
> >
> > --
> >
> > Dave Peterson
>
> --
> mainemike

--

Dave Peterson