Odd behavior with SaveAs

[Guest]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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.