Cannot get "Last Save Time" from other file

[Mustangdave05]

I have been searching everwhere for an answer and have tried numerous examples of code found on the internet. Nothing seems to work. This is what I am trying to do...

I have a workbook that retrieves data from a file from a URL address upon opening. Actually, I am saving the URL file to a local drive and then updating my workbook with this data. The problem is that the job that updates the URL file sometimes fails. I want to notify the user of my workbook that the data has not been update recently. I am trying to use the built in document properties (Last Save Time) but I cannot get it to work except for the workbook that has the macros in it. Is there a way to find the last save time of a workbook (from a URL address) seperate from the workbook that has the marco? Below is my current code that errors out at: dp = Application.Workbooks("DockReportExport.xls").BuiltinDocumentProperties("Last Save Time") with and runtime error.

Thanks for your help in advance!!!!

Sub FTP()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Open file from URL addres to check last save time
Workbooks.Open Filename:="http://172.16.1.94/Files/Operations/Dock/LoadBuilder/DockReportExport.xls"
'Check if data on server has been updated in the last 15 minutes
Dim dp
dp = Application.Workbooks("DockReportExport.xls").BuiltinDocumentProperties("Last Save Time")
Dim cd
cd = Now() '- 0.0104 '0.0104 = 15 minutes
ActiveWorkbook.Close SaveChanges:=False

If dp < cd Then 'ask if user wants to continue or stop
Response1 = MsgBox("Data on server has not been update since " & dp & "." _
& vbCr & "Select YES to continue using data from server." & vbCr _
& "Select NO to stop.", 4, "Old data on server!", 0, 0)

If Response1 = vbNo Then
goto 10
End if
End if


'download file from URL and save to local drive
Dim ret As Long
ret = URLDownloadToFile(0, "http://172.16.1.94/Files/Operations/Dock/LoadBuilder/DockReportExport.xls", "c:\LoadBuilder\DockReportExport.xls", 0, 0)

'Open local file and copy date to my workbook
Workbooks.Open Filename:="c:\LoadBuilder\DockReportExport.xls"
Range("$A:$BB").Select
Selection.Copy
Windows("Truck Builder.xls").Activate
Sheet10.Visible = xlSheetVisible
Sheets("Dock Report").Select
Range("B1").Select
ActiveSheet.Paste
Workbooks("DockReportExport.xls").Worksheets("Transport - Merge Report").Activate
Application.CutCopyMode = False
ActiveWindow.Close SaveChanges:=False
Sheets("TRUCK BUILDING").Select
Sheet10.Visible = xlSheetVeryHidden
Application.DisplayAlerts = True
Application.ScreenUpdating = True

10 End sub

 

[VoG]

I imagine that the workbook whose properties you are querying has to be open. Maybe

Workbooks.Open Filename := "DockReportExport.xls"
dp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
Workbooks("DockReportExport.xls").Close False

 

[Mustangdave05]

Thanks for your suggestion VoG. However, I do have the file open and active at the time I check the last save date. I even tried your code:

dp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

and I got the same error. Any other ideas?

 

[VoG]

Code like this works for me. What is the error message?

 

[Mustangdave05]

I get:
Run-time error '-2147467259(80004005)':
Automation error
Unspecified error

BTW: I am using Windows XP and Excel 2003. I thought it was an issue because I was opening the file from a URL but I have changed the code to open a file on the local hard drive and get the same error.

 

[Jester75]

Mustangdave, I think this error occurs when you have two sheets open.

I have been trying to get the same sort of code to work as I need to verify if a file has been updated on the current day. The problem with the file is that we are generating it from a another source and use "Replace File" so it kills any code we may try to place within itto save a date on close.

A bit frustrating..

I feel that if I can specify the file I wish to return the properties from then it would be ideal, but excel seems to only recognise the properties of the file that has the VBA code in it.

I have tried using:

Dim fso As Object
Dim fsoFile As Object
Dim strFile As String
Dim LastSaveDate1 As Date

strFile = "MY FILE PATH" & "\" & "MY FILE"

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFile = fs.GetFile(strFile)

On Error Resume Next
LastSaveDate1 = CDate(fsoFile.DateLastModified)

but I get a 424 error instead.

If anyone has any ideas I would also be very appreciative.