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\PostingS um.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 wrote:

I am using Lou Kapeller application, published on the UtterAccess
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 excel
spreadsheet file goes here
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("Sheet1") 'Name
of sheet you want to export to
objBook.Windows(1).Visible = True
Set rst =
db.OpenRecordset("QryPostingSumPayrollFinal") 'Opens the recordset and
sets the variable
With objSheet
Select
Range("a2:h500").Clear 'Clears the current data in
the workbook range
Range("A2:h2").CopyFromRecordset rst 'rst Copies the
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 spreadsheet
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 leaves
open the new textfile. It also behaved like that.
My original macro without the date was as follows, it never changed the
xlt, created the iif file and displayed the message, and after the user
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, Saving
and closing Workbook"
ActiveWorkbook.Close
End If
End Sub

Anne

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