Date using VBA , Excel Pivot

[imfarhan]

Hi All,

I have application which creates the excel reports using Ms Excel Template , the template called "abc.xls" and the output file which generates every day will be called "abc_01062011.xls" automatically.

The abc file has two sheets Data and Pivot
In pivot sheet I have the formula =Today() whic pulls today system date.
but the problem is that when the user open the report some other days' it shows system/Today's date instead of the day when the file was created.

I have used the following VBA code on the Pivot_Sheet

Private Sub Workbook_Open()

Range("D1").Value = Format(ThisWorkbook.BuiltinDocumentProperties("Creation Date"), "short date")
'Range("D1").Value = Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "short date")
End Sub

The first line i above VBA code for "Creation date" and the 2nd line is for when the file updated. When the application generates the output reports it shows the Date when the "Template" was created instead of the new file(abc_01062011) which created today.

I have also used the "Last Save Time" but it doesn't like that as well and pulls "bring the last update date of the Template again instead of actual generated report which is abc_01062011.xls

Is there any other way withouth VBA , coz Today() functions works OK on "Data" sheet where no Pivot exist.

I hope it does make sense to all
I appreciate your help

Regards

Farhan

 

[Fazza]

Hi, Farhan

It doesn't quite make sense to me. Though I'm still sure whatever you want can be done. With VBA or even without.

With VBA might be better. Perhaps when the file is created to change the =TODAY() cell's entry to a value. Such as if the cell has a defined name - so that the code will always reference the same cell regardless of row/column insertions/deletions - like

range("YourCellDefinedName").value = clng(date)
or something like that.

Without VBA, you could have a formula - either in a worksheet or defined name - that takes the text from the end of the file name and converts that to a date.

HTH. regards

 

[imfarhan]

Thaks for the response.

I have change my VBA code using "Define" function I think its working but I will check tomorrow how the applicatin will handle it when The Excel template runs live.

I have moved my VBA code to "ThisWorkBook" instead of sheet1(Pivot) becaue define "define_dt" can be call from any where.
so my VBA code is as follow:-

Private Sub Workbook_Open()
   Range("d_define").Value = CLng(Date)
End Sub

So will give you an update tomorrow.

Thanks for you help!
Farhan

 

[imfarhan]

Thanks again Fazza
I check the reports its working fine today
Regards
F