Macro file save as, saving sheet not workbook

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.

You're saving as an .iif file? What's that?


"annep" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

".iif" is actually a tab delimiter text file. This file is for import into Quickbooks. I could use the same file and make it a ".txt", it just one more step when selecting the import into Quickbooks.

Previously I used this routine without the addition of the date and if worked fine:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Access\PayrollPosting.iif", FileFormat _
:=xlText, CreateBackup:=False
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True

Thanks,
Anne

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

Dave,
Where I record a macro while copying 2 sheets in a new workbook
,converting the formula to values & saving as a new file I get a macro
in the new file call "Record1" & I can't delete it - Is it a bug or
harmful for the new file??

Thxs

It's probably not harmful -- but it could cause trouble for the person opening
the file. They might be confused about the "this file contains macros" warning
or even worse, they could run it.

But I've never seen a recorded macro get named Record1 -- unless I changed the
name. Mine are named Macro1, Macro2, ...

Are you recording a macro in that new workbook? If you are, then you could
always store the recorded macro in a different workbook.

And if your worksheets have code behind them (say for events or controls), then
that code will get copied with the worksheet.

I'd be very hesitant to call it a bug. Most bugs I see are just my errors.

al007 wrote:
>
> Dave,
> Where I record a macro while copying 2 sheets in a new workbook
> ,converting the formula to values & saving as a new file I get a macro
> in the new file call "Record1" & I can't delete it - Is it a bug or
> harmful for the new file??
>
> Thxs

--

Dave Peterson

Dave,
I think I skipped some important info.
The data comes from Access. I run an event in Access, which transfer data from Access to Excel. Access opens the Postingsum.xlt and copies the data from an access query into the xlt file. The user then runs the macro which converts the data to a format that Quickbooks accepts. It then saves the file as the tab delimter text file using the .iif extension. The xlt never gets changed.

I tried your macro and if works well, but it does not close the template.

Of course even nicer would be, if the macro could be invoked directly from Access or run an automacro. Just have not figured out how to do that, since it cannot be in the on open event of the xlt file since the copying of the data has to take place first.


Anne

Dave,
I have your macro saving the iif.file. I then had to add Close the xlt file.
I just can't get it to close excel.
I added the following lines
ActiveWorkbook.Close
Application.Quit

the close works, but the quit does not.
Anne