Odd behavior with SaveAs
 

I have an Excel 2000 workbook with 6 worksheets and 1 code module
My code is designed to save each of the worksheets as individual CSV files

After adding a menu item and linking it to my VBA code, the code works a
expected save for one thing; it is saving/renaming the main Excel workboo
as a CSV file with the same name as the last worksheet name, which is not desireable.

Thinking the loop version was somehow including the workbook name, I hardcoded
the worksheet names in my code, but it made no difference

Here's the code
' Loop version
For Each wsTemp In Worksheet
wsTemp.SaveAs Filename:=wsTemp.Name, FileFormat:=xlCSVWindow
Nex

' Brute-force version
Worksheets("Customer").SaveAs Filename:="Customer", FileFormat:=xlCSVWindow
Worksheets("Worksite").SaveAs Filename:="Worksite", FileFormat:=xlCSVWindow
Worksheets("Order").SaveAs Filename:="Order", FileFormat:=xlCSVWindow
Worksheets("Assignment").SaveAs Filename:="Assignment", FileFormat:=xlCSVWindow
Worksheets("Employee").SaveAs Filename:="Employee", FileFormat:=xlCSVWindow
Worksheets("Invoice Detail").SaveAs Filename:="Invoice Detail", FileFormat:=xlCSVWindow

Any help is greatly appreciated

Cheers
d

Odd behavior with SaveAs
 

Is your problem due to the fact that after doing a SaveAs, the
activeworkbook assumes that name. So at the end, the activeworkbook is
Employee.csv?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dj" wrote in message
...
I have an Excel 2000 workbook with 6 worksheets and 1 code module.
My code is designed to save each of the worksheets as individual CSV
files.

After adding a menu item and linking it to my VBA code, the code works as
expected save for one thing; it is saving/renaming the main Excel workbook
as a CSV file with the same name as the last worksheet name, which is not
desireable.

Thinking the loop version was somehow including the workbook name, I
hardcoded
the worksheet names in my code, but it made no difference.

Here's the code:
' Loop version:
For Each wsTemp In Worksheets
wsTemp.SaveAs Filename:=wsTemp.Name, FileFormat:=xlCSVWindows
Next


' Brute-force version:
Worksheets("Customer").SaveAs Filename:="Customer",
FileFormat:=xlCSVWindows
Worksheets("Worksite").SaveAs Filename:="Worksite",
FileFormat:=xlCSVWindows
Worksheets("Order").SaveAs Filename:="Order", FileFormat:=xlCSVWindows
Worksheets("Assignment").SaveAs Filename:="Assignment",
FileFormat:=xlCSVWindows
Worksheets("Employee").SaveAs Filename:="Employee",
FileFormat:=xlCSVWindows
Worksheets("Invoice Detail").SaveAs Filename:="Invoice Detail",
FileFormat:=xlCSVWindows


Any help is greatly appreciated!

Cheers,
dj

Odd behavior with SaveAs
 

Dim sh as Worksheet
for each sh in Activeworkbook.worksheets
sh.copy ' puts copy of sh in a new workbook which is now
' the activeworkbook
activeworkbook.Saveas Filename:=ActiveSheet.Name, FileFormat:=xlCSVWindows
' close without prompt - it has already been saved
activeworkbook.Close Savechanges:=False
Next

--
Regards,
Tom Ogilvy

"dj" wrote in message
...
I have an Excel 2000 workbook with 6 worksheets and 1 code module.
My code is designed to save each of the worksheets as individual CSV
files.

After adding a menu item and linking it to my VBA code, the code works as
expected save for one thing; it is saving/renaming the main Excel workbook
as a CSV file with the same name as the last worksheet name, which is not
desireable.

Thinking the loop version was somehow including the workbook name, I
hardcoded
the worksheet names in my code, but it made no difference.

Here's the code:
' Loop version:
For Each wsTemp In Worksheets
wsTemp.SaveAs Filename:=wsTemp.Name, FileFormat:=xlCSVWindows
Next


' Brute-force version:
Worksheets("Customer").SaveAs Filename:="Customer",
FileFormat:=xlCSVWindows
Worksheets("Worksite").SaveAs Filename:="Worksite",
FileFormat:=xlCSVWindows
Worksheets("Order").SaveAs Filename:="Order", FileFormat:=xlCSVWindows
Worksheets("Assignment").SaveAs Filename:="Assignment",
FileFormat:=xlCSVWindows
Worksheets("Employee").SaveAs Filename:="Employee",
FileFormat:=xlCSVWindows
Worksheets("Invoice Detail").SaveAs Filename:="Invoice Detail",
FileFormat:=xlCSVWindows


Any help is greatly appreciated!

Cheers,
dj

Odd behavior with SaveAs
 

When you do a saveas manually and choose a single sheet format, the actual
workbook doesn't really look any different although it does take on the name
that you saved with. If you then close the workbook and reopen it, you will
find that it is in the format you save it with - single sheet (all your
other sheets/code are gone).

I suspect when you use worksheet as the object for the saveas, you are doing
much the same. The workbook looks the same, and has the same name, but if
you close it and reopen it, it will be transformed.

That would be my guess.

--
Regards,
Tom Ogilvy


"dj" wrote in message
...
Bob: Yes, that's what happens. KB article 213781 sort of touches on it,
but doesn't really help.
Tom: Thanks for the workaround. Any idea why my original method does what
it does?
After I applied Office 2k sp3, I tried a new, empty workbook and got
exactly the same behavior.
I sent MS a description of what I believe is a bug.

Thanks for replying!
Cheers,
dj