Macro file save as, saving sheet not workbook

[annep]

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.

 

[Barb Reinhardt]

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

 

[Dave Peterson]

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.

 

[al007]

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]

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.

 

[annep]

".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

 

[annep]

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

 

[annep]

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

 

[Dave Peterson]

Do you really open the .xlt file or do you create new workbook based on that
..xlt template?

dim newwkbk as workbook
set newwkbk = workbooks.add(template:="c:\somefolder\Postingsum.xlt")
'do the work
'and close the .iif file
newwkbk.close savechanges:=false
set newwkbk=nothing
'set allyourotherobjects = nothing
application.quit

======
If you're opening excel, then ok. But if you're using an already running
instance of excel, I'd be very hesitant to close the application.

 

[annep]

I am using Lou Kapeller application, published on the UtterAcces
newgroup.

Private Sub cmdTransferDataToExcel_Click()
On Error Resume Next
Dim db As Database
Dim rst As Recordset
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim Path As String

Set db = CurrentDb()

Set objBook
Workbooks.Add(Template:="C:\Access\PostingSum.xlt") 'Your exce
spreadsheet file goes here
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("Sheet1") 'Nam
of sheet you want to export to
objBook.Windows(1).Visible = True
Set rst
db.OpenRecordset("QryPostingSumPayrollFinal") 'Opens the recordset an
sets the variable
With objSheet
.Select
.Range("a2:h500").Clear 'Clears the current data i
the workbook range
.Range("A2:h2").CopyFromRecordset rst 'rst Copies th
recordset into the worksheet
End With
rst.Close
objApp.Visible = True
Set rst = Nothing
Set db = Nothing
Set objSheet = Nothing
Set objBook = Nothing
Set objApp = Nothing
End Sub

When this is finished I have the PostingSum.xlt open in Excel.
Then the user has to initiate the macro which formats the spreadshee
and the macro then saves the new data to the postinsum.iif.

I thought that when I do a "save as", it closes the xlt and it leave
open the new textfile. It also behaved like that.
My original macro without the date was as follows, it never changed th
xlt, created the iif file and displayed the message, and after the use
clicked ok on the message box, excel closed:
...
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Access\Postingsum.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, Savin
and closing Workbook"
ActiveWorkbook.Close
End If
End Sub

Ann

 

[Dave Peterson]

After reading what you're doing, it sounds like you could drop all that stuff
that I suggested. After you save the file (as .IIF), you can just close the
file and not worry about the template.

Does that second snippet of code exists in the template? If it does, then I
think that this will work. If it doesn't exist in that template, then I would
think it would make much more sense there. (In fact, I'd put a button from the
Forms Toolbar on Sheet1 of the template.

When you save the file as a text file, then the button won't come with it--and
neither will the code.

It checks to see how many workbooks are open (including hidden workbooks). If
it sees more than one, then it just closes that workbook. If it only sees one,
it closes the application. In both cases, the file was already saved as .IIF.)

Sub doTheWork()

Dim WorkbookCtr As Long
Dim wCtr As Long
Dim wkbk As Workbook

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:="C:\Access\Postingsum.iif", _
FileFormat:=xlText, CreateBackup:=False
Application.displayalerts = true

MsgBox "The Posting Summary for this week has been created, " & _
"Saving and closing Workbook"

If Workbooks.Count > 1 Then
ThisWorkbook.Close savechanges:=False
Else
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

I know nothing about access, but this is a way you can check to see if there is
already an instance of excel running--then you can use that instance to
manipulate the data. (It compiled ok--after a couple of de-Accessifying line
changes, but I didn't test it.)

Option Explicit
Private Sub cmdTransferDataToExcel_Click()

Dim db As Database
Dim rst As Recordset
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim Path As String
Dim XLWasRunning As Boolean

XLWasRunning = True
On Error Resume Next
Set objApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objApp = CreateObject("Excel.Application")
XLWasRunning = False
End If
On Error GoTo 0

objApp.Visible = True

Set db = CurrentDb()

Set objBook = objApp.Workbooks.Add(Template:="C:\Access\PostingSum.xlt")
Set objSheet = objBook.Worksheets("Sheet1")

Set rst = db.OpenRecordset("QryPostingSumPayrollFinal")

objSheet.Range("a2:h500").Clear
objSheet.Range("A2:h2").CopyFromRecordset rst

rst.Close

Set rst = Nothing
Set db = Nothing
Set objSheet = Nothing
Set objBook = Nothing
Set objApp = Nothing

End Sub

 

[annep]

I really like the button idea, didn't even know I could do that.
I am still trying to save the iif file with PostingSum & date.iif.
I put your codes together, but I again changes the sheet name to
PostingSum011706, but the sheet name needs to stay constant because
Access looks for the sheet name.
Any other ideas?
Anne

Sub doTheWork()
Dim sStr As String
Dim WorkbookCtr As Long
Dim wCtr As Long
Dim wkbk As Workbook
Const sDateCell As String = "c4"
sStr = Format(Range(sDateCell), "mmddyy")
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:="C:\Access\Postingsum" & sStr & ".iif",
_
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True

MsgBox "The Posting Summary for this week has been created, " & _
"Saving and closing Workbook"

If Workbooks.Count > 1 Then
ThisWorkbook.Close savechanges:=False
Else
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

 

[annep]

I am loosing it. It works, because the xlt file stays unchanged, (I
forgot to mention, that the macro is in the template)

It changes the sheet name on the iif file, which does not matter.
Thanks, a lot
Anne

 

[Dave Peterson]

Why does Access care?

I thought that the second code formatted, saved and closed the text (.iif) file.

And once you save (and close) that workbook as a text file, there isn't a
worksheet anymore--it's just plain old text.

==
Since I'm obviously missing something basic, you could either use the copy to
new workbook and save from there or just rename the sheet after you do the save.

thisworkbook.saveas....
Thisworkbook.worksheets(1).name = "Sheet1"
(since there's only one sheet in the template.)

 

[Dave Peterson]

Please ignore my previous message. Now we're cooking with gas--and at these
rates, we better be making something good! (huh???).

 

[annep]

Yes, I am really pleased, you taught me a lot.
Thanks again,
Anne