Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel VBA debugging help needed!
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA debugging help needed!
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA debugging help needed!
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 |
#4
|
|||
|
|||
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! Quote:
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA debugging help needed!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel Help Needed | Excel Worksheet Functions | |||
Display form from an VB application in Excel | Excel Discussion (Misc queries) | |||
Is anything needed if I only load Word & Excel from Office XP Sta. | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |