When you save an excel workbook as a text file, you can only save that
activesheet. And one of the consequences is that worksheet gets renamed--it
even happens if you do it manually.

You've got a couple of choices...

Save the name of the worksheet in some variable, do your SaveAs and rename the
worksheet. But the problem with that is the current workbook is now the .iif
(or .txt) file. You could mess up and not save the latest changes as a normal
..xls workbook.

The other option is to copy that worksheet to another workbook, save that
workbook as your text file and then close that workbook--the nice thing about
this is that you still have that original workbook in the same state as before
you saved.

Option Explicit
Public Sub PostingSumSave()

Dim sStr As String
dim Wks as worksheet
Const sDateCell As String = "c4"
Const SPath As String = "C:\access\"

sStr = Format(Range(sDateCell), "mmddyy")

activesheet.copy 'to a new workbook
set wks = activesheet

application.displayalerts = false
wks.parent.SaveAs Filename:=SPath & "PostingSum" & sStr & ".iif", _
FileFormat:=xlText, CreateBackup:=False
application.displayalerts = true
wks.parent.close savechanges:=false

'do you still want/need to save the workbook?
MsgBox "The Posting Summary for this week has been created" & vblf & _
"Saving and closing Workbook"

End Sub

I didn't test this, but it compiled ok.

annep wrote:

I am using the below routine, which I found in this newsgroup, but
instead of saving the file as Postingsum011706.iif, it renames the
current sheet, I don't want to change the worksheet name, I want to
change the file name for Postingsum plus date, which is located in
field C4.

Public Sub PostingSumSave()

Dim sStr As String
Const sDateCell As String = "c4"
Const SPath As String = "C:\access\"
sStr = Format(Range(sDateCell), "mmddyy")
ThisWorkbook.SaveAs Filename:=SPath & "PostingSum" & sStr & ".iif", _
FileFormat:=xlText, CreateBackup:=False
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
Application.DisplayAlerts = True
MsgBox "The Posting Summary for this week has been created, Saving
and closing Workbook"
ActiveWorkbook.Close

End Sub

I am also having problems suppressing the save messages, but I think
that is related to it renaming the sheet, then of course it is asking
to save the workbook.

--
annep
------------------------------------------------------------------------
annep's Profile: http://www.excelforum.com/member.php...o&userid=18851
View this thread: http://www.excelforum.com/showthread...hreadid=503559

--

Dave Peterson