Returning to Original Workbook After Saving Sheets

[Barry]

Greetings:

I have a VBA routine that saves each worksheet in the active workbook as a
..csv file. When finished, I want the original workbook to be active, with
its original name. In order to accomplish this, I must end the routine by
saving the workbook one more time, using its its original name. This is
undesirable as the user may wish to save the sneets but not save the whole
workbook.

Here is the code for the routine

#########
Sub saveallCSV()
Dim Fname As String
Dim OrigFname As String
Dim Fpath As String
Dim sht As Worksheet
OrigFname = ActiveWorkbook.Name
Fpath = ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In Sheets
Fname = Fpath + "\" + sht.Name + ".csv"
sht.SaveAs Fname, FileFormat:=xlCSV
Next sht
Fname = Fpath + "\" + OrigFname
ActiveWorkbook.SaveAs Fname, FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
#########

How can I eliminate the last save and still return to the original workbook?

Thanks in advance for your help.

Barry Carroll
PSC Scanning
(e-mail address removed)

 

[Dave Peterson]

Maybe you could just copy each sheet to a new workbook, then save that workbook
as a .csv file.

Here's one that you can modify:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

set wks = worksheets("whateveroneyouwanthere")

wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks
Application.DisplayAlerts = False
.Parent.SaveAs Filename:="C:\TEMP\" & .Name & ".csv", _
FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With

End Sub

 

[Barry]

Dave:

Your solution worked perfectly. Thanks very much.
--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any statements
made by me. I''m entirely on my own.

Maybe you could just copy each sheet to a new workbook, then save that workbook
as a .csv file.

Here's one that you can modify:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

set wks = worksheets("whateveroneyouwanthere")

wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks
Application.DisplayAlerts = False
.Parent.SaveAs Filename:="C:\TEMP\" & .Name & ".csv", _
FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With

End Sub