I'm exporting an Access query to an excel template using the TransferSpreadsheet method. I want to capture the date/time of the export and keep it static. I currently have a cell with Now() in it, but everytime I open the workbook the date gets updated. How can I correct this?

Any help is appreciated!

Thanks,
Sup

One method would be to include the date/time in the name of the spreadsheet file that your create.

Also depends whether you will continue export (more than one time), which would require cell reference to date.

The XL file is saved with a name that includes the date. The spreadsheet is passed around and updated by several people and then ultimately printed as a report. I need the report to include a printed cell with the original date of export.

I created an XL macro to copy the date cell and paste back as special-value when the spreadsheet is opened. It sort of works, but the disable macro warning comes up each time you open the spreadsheet, which will cause me headaches that I don't need. There must be a better way?

Thanks,
Sup

Perhaps you could export the date as a text string from within Access?
something like:

Me.YourTextField = "Export Date = " & Format(Me.YourDateField, "mmmm dd, yyyy")

That way, Excel will not automatically try to update it as a date since it is stored in a text field.

You could put this into your Personal.xls and it would be available in any workbook.

Sub InsertDate()
Activecell=Date
End Sub

I've transfered the XL macro code to the Access sub that runs the transfer spreadsheet:


Dim stDocName As String

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook

Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\ReportTemplate.XLT")
'*****Fix Date cells*****************
objXLBook.Sheets("Report1").Select
Range("B7:C7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
objXLBook.Sheets("Report2").Select
Range("B7:C7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'***End fix date cells****************
objXLBook.SaveAs (Me.txtSaveFile)
objXLBook.Close

stDocName = "Report1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, stDocName, Me.txtSaveFile, True
stDocName = "Report2"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, stDocName, Me.txtSaveFile, True
Set objXLApp = Nothing


This does the trick, but leaves a Excel.exe task process open that gets in the way of other things.

How do I end/close/remove the open Excel.exe in code?

Thanks,
Sup

I've read many posts without a solution to this "Hanging Excel.exe Process" problem. I solved my particular issue by moving the copying & pasting to after the transferspreadsheet then saving and quiting the workbook and then quiting the application.

Thanks to all whose posts helped me figure out this and many other issues!
Sup