Date Stamp when info is updated

[NervousFred]

I have an excel table that is imported from Access. I have a column named
Price and I need a column next to it called Date that will show when that
specific price is updated.

I have been searching around and most of the things I have come across have
not been working becuase everythime I update the entire table the dates
change with the table.

I need something that will only change when a specific cell changes.

Thanks!

 

[XP]

Hi,

I have handled this before by including an "Update" date column in the
MS-Access table, and then capture the date and time each record is updated in
MS-Access. Then when you import your records into Excel, the info is already
there...perhaps this would work?

 

[NervousFred]

Hrmm didn't think of that.

How would I do that in a query in access? My data that I use to import into
excel is a query of a larger database.

Also would each date remain the same and only update if its corrisdoning
cell updated or would all of the dates update each time the query was
refreshed.

As I said earlier, I just need each date to update ONLY if the price next to
it CHANGES.

 

[Rick Rothstein \(MVP - VB\)]

Assuming your Price column is Column C and your Date column is Column D,
then give this worksheet event code a try. To install it, right-click the
tab for the worksheet containing your table and copy/paste the following
code into the code window that appeared...

'********** START OF CODE **********
Dim CellValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Target.Value <> CellValue Then Cells(Target.Row, "D").Value = Now
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then CellValue = Target.Value
End Sub
'********** END OF CODE **********

Now, whenever you change a value in Column C, the current date/time will be
entered in the same row in Column D. There are two logical statements for
Column C (they are Target.Column=3) that you will need to change in case I
guessed wrong as to which column has your Prices in it.

Rick

 

[Rick Rothstein \(MVP - VB\)]

In looking at the other sub-thread to your posting, I think the answer I
gave you is not what you are looking for. The code I posted assumes you
loaded your data from wherever and will insert a date in response to you
changing a single cell by typing in a new value. If you are reloading new
information over top of the old data, the code I posted won't work for you.

Rick

 

[FSt1]

hi
sounds like the price needs to be updated in access and the date of update
entered into access THEN downloaded to excel.

Regards
FSt1

 

[NervousFred]

Rick,

Yes, I have the table set to refresh everytime the spreadsheet is opened.
This will affect the dates in your code correct?

Anyone know how to do the time stamps in Access? If not I can just always
post in the access forums.