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
--
anne
-----------------------------------------------------------------------
annep's Profile:
http://www.excelforum.com/member.php...fo&userid=1885
View this thread:
http://www.excelforum.com/showthread.php?threadid=50355