static date in cell? (1 Viewer)

[supmktg]

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

 

[CraigDolphin]

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

 

[shades]

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

 

[supmktg]

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

 

[CraigDolphin]

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.

 

[shades]

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

Sub InsertDate()
   Activecell=Date
End Sub

________
Marijuana vaporizer

 

[supmktg]

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

 

[supmktg]

Hanging Process Fixed

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