Re: Macro file save as, saving *** not workbook

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

You've got a couple of choices...

Save the name of the work*** in some variable, do your SaveAs and rename the
work***. 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 work*** 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 work***
Const sDateCell As String = "c4"
Const SPath As String = "C:\access\"

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

active***.copy 'to a new workbook
set wks = active***

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 ***, I don't want to change the work*** 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 ***, then of course it is asking
> to save the workbook.
>
> --
> annep
> ------------------------------------------------------------------------
> annep's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18851
> View this thread: http://www.excelforum.com/showthread.php?threadid=503559

--

Dave Peterson
.

Loading