filedatetime function

See if this line of code is what you are after; it captures the built in property of Last Save Time. Otherwise, I'm not sure of what you're after exactly, but hope this helps.

Sub WhenSaved()
MsgBox "This file was last saved on " & Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "mmmm d, yyyy") & ".", 64, "Date of most recent save:"
End Sub

Thanks for the reply.

I am importing data from a text file that reside on our intranet.

It appears the code referenced above would require first opening the file and I was hoping to avoid that. I just want to retrieve the file date information so I can report it on the spreadsheet. The FileDateTime function works if I first copy the file to either my local drive or to any of the servers in our company. The problem seems to be that this function does not recognize "http" as the path name or I am entering it wrong or am missing something. I was thinking I had to somehow identify this path as being an internet address (Hyperlink function?????) but did see how that was possible.

If I open the file from the internet directly into Excel, ActiveWorkbook.Path. ActiveWorkbook.Name and ActiveWorkbook.FullName all return the “http” address. But even with this workbook opened, I still cannot get the FileDateTime function to work even when substituting any one or a combination of the above functions for the “path”.

So, do I need something else beside just this function?? Or maybe this cannot be done with this function and if not is there another way of retrieving a file date from a file (without opening it) that has an internet address??

Thanks,

FileDateTime of a URL?

...The FileDateTime function works if I first copy the file to either my local drive or to any of the servers in our company. The problem seems to be that this function does not recognize "http" as the path name or I am entering it wrong or am missing something...

Click to expand...

I am having this exact problem. This is an old thread, but no solution was ever posted. Can someone suggest a solution?

I want to check the last modified date of an excel file at an intranet site without opening it. This is a big time savings for my application as I need to monitor many excel files for updated content.

Putting a URL into FileDateTime() is giving me Run-time error "5": Invalid procedure call or argument.

Paul

Hi Paul

I'm not certain what I'm suggesting will be much assistance, but what's the UNC path of the file? Something along the lines of:


Richard

UNC?

I have been specifying the path as an http. It is a long path (~180 characters).

Are you suggesting that I try to convert the http path to a UNC format? Do I do this just by dropping the "http:" and reversing the slashes?

Paul

I guess you may not even have access to it via a network path, but it would be worth finding out. I suspect it won't be as simple as removing the http and reversing the slashes, but I could be wrong! Are there some IT people at work you could find this out from? There should be WindowsAPI functions you can use to get the File Attributes (even without opening the file) if you can specify a UNC. There may well be some server-side scripts you can use to determine the info you want from the existing http address, but that's not an area I've ever got involved with.

Richard

Re: FileDateTime of a URL?

[I want to check the last modified date of an excel file at an intranet site without opening it.

Click to expand...

Taking a break during halftime of the day's first round of (yes count 'em 42 !) satellite TV college football games and I noticed my name in this resurrected thread.

I don't think you need to open the files to get their properties. Gently plugging the 2500 examples disk, this macro from Hans Herber lists the properties of files in a given path that you enter into cell E1.

Example, in E1 just now I entered
C:\Your\File\Path
which is a valid path on my computer.

Then I executed this macro and properties for all files in that folder were listed, including the last modified property you are interested in. You can further modify the code to delete information for all non-Excel listed files, or to only show the file name you are interested in, but I just tested this macro now as I did when I edited it for the disk (file #209103) and it works fine, no problems.

All files that reside in the path specified in cell E1 shall have their properties and information listed, categorized and displayed in columns A:C.



Sub ExtendedFileInfo()

Dim MyPath As String
MyPath = Range("E1").Value
If Len(Dir(MyPath, vbDirectory)) = 0 Or Len(Range("E1")) = 0 Then
MsgBox "No valid path exists in cell E1 !!", 48, "No such animal"
Exit Sub
End If

Dim objShell As Object, objFolder As Object
Dim iCounter As Long, iRow As Long, iCol As Integer
Dim strFileName As Variant
Dim arrHeaders(34)

Application.ScreenUpdating = False
Range("A:C").ClearContents

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(Range("E1").Value)
iRow = 3
iCol = 1

For iCounter = 0 To 33
arrHeaders(iCounter) = objFolder.GetDetailsOf(objFolder.Items, iCounter)
Next iCounter

For Each strFileName In objFolder.Items
For iCounter = 0 To 33
Cells(iRow + iCounter, 1).Value = iCounter + 1
Cells(iRow + iCounter, 2).Value = arrHeaders(iCounter)
Cells(iRow + iCounter, 3).Value = objFolder.GetDetailsOf(strFileName, iCounter)
Next iCounter
iRow = iRow + 35
Next strFileName

Range("A:C").Columns.AutoFit
Application.Goto Range("A1"), True
Application.ScreenUpdating = True

End Sub

[SOLVED] FileDateTime() replaced by user Function

Tom - the issue was getting the modified date from a URL filespec. I found the solution at http://www.pcreview.co.uk/forums/thread-1437578.php. The gsGetLastModifiedDate() directly replace FileDateTime() in my macro. Credits to Jake Marx, MS MVP - Excel, www.longhead.com.

---------------------------------------------------------------------------

Public Function gsGetLastModifiedDate(rsURL As String) As String
Dim x As Object

On Error GoTo ErrHandler

Set x = CreateObject("MSXML2.XMLHTTP.5.0")

x.Open "HEAD", rsURL, False
x.send
gsGetLastModifiedDate = x.getResponseHeader("Last-Modified")

ExitRoutine:
Set x = Nothing
Exit Function
ErrHandler:
Resume ExitRoutine
End Function

Also. You are dependant on the server providing this information. Not all servers provide it.