We have files which contain management information, alway have the same
names and art stored in a known location on the intranet - eg

http://thepulse/_uklife/_services/_m.../SBB%20MMM.xls

Is there any way I can get Excel to find out the last saved date of
these files, so I can compare with known saved dates and thus automate
the updating of local reports IF the data has changed?

Here is some code for you. You MUST reference "Microsoft Scripting Runtime"
to use this code. (Tools - References -...). Run the procedure called test.
It will search the directory specified (and all subdirectories if the final
argument is true) and populate sheet 1 with the file name, creation date and
last modified date...

Option Explicit
Option Compare Text

Sub test()
Call ListFiles("C:\Test", Sheet1.Range("A2"), "xls", True)
End Sub

Public Sub ListFiles(ByVal strPath As String, _
ByVal rngDestination As Range, Optional ByVal strFileType As String = "*", _
Optional ByVal blnSubDirectories As Boolean = False)
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strName As String

'Specify the file to look for...
strName = "*." & strFileType
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
rngDestination.Value = objFile.Path
rngDestination.Offset(0, 1).Value = objFile.DateLastAccessed
rngDestination.Offset(0, 2).Value = objFile.DateLastModified
Set rngDestination = rngDestination.Offset(1, 0)
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
If blnSubDirectories = True Then _
DoTheSubFolders objFolder.SubFolders, rngDestination, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub


Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef rng As Range, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

On Error GoTo ErrorHandler
For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
rng.Value = scrFile.Path
rng.Offset(0, 1).Value = scrFile.DateLastAccessed
Set rng = rng.Offset(1, 0)
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, rng, strTitle
End If
ErrorHandler:
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'-------------------

--
HTH...

Jim Thomlinson


" wrote:

We have files which contain management information, alway have the same
names and art stored in a known location on the intranet - eg

http://thepulse/_uklife/_services/_m.../SBB%20MMM.xls

Is there any way I can get Excel to find out the last saved date of
these files, so I can compare with known saved dates and thus automate
the updating of local reports IF the data has changed?

Thanks for the help Jim, and I agree that what you have given me works
fine when the file path is local (c:\, or mapped g:\ or whatever) BUT
my problem is that the file is saved on the intranet, the only known I
have at the moment is the http:\\ location - I guess in desperation I
could search the servers till I find the ACTUAL location, but I'm
HOPING to do it using the http:\\ location


Jim Thomlinson wrote:
Here is some code for you. You MUST reference "Microsoft Scripting Runtime"
to use this code. (Tools - References -...). Run the procedure called test.
It will search the directory specified (and all subdirectories if the final
argument is true) and populate sheet 1 with the file name, creation date and
last modified date...

Option Explicit
Option Compare Text

Sub test()
Call ListFiles("C:\Test", Sheet1.Range("A2"), "xls", True)
End Sub

Public Sub ListFiles(ByVal strPath As String, _
ByVal rngDestination As Range, Optional ByVal strFileType As String = "*", _
Optional ByVal blnSubDirectories As Boolean = False)
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strName As String

'Specify the file to look for...
strName = "*." & strFileType
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
rngDestination.Value = objFile.Path
rngDestination.Offset(0, 1).Value = objFile.DateLastAccessed
rngDestination.Offset(0, 2).Value = objFile.DateLastModified
Set rngDestination = rngDestination.Offset(1, 0)
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
If blnSubDirectories = True Then _
DoTheSubFolders objFolder.SubFolders, rngDestination, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub


Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef rng As Range, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

On Error GoTo ErrorHandler
For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
rng.Value = scrFile.Path
rng.Offset(0, 1).Value = scrFile.DateLastAccessed
Set rng = rng.Offset(1, 0)
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, rng, strTitle
End If
ErrorHandler:
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'-------------------

--
HTH...

Jim Thomlinson


" wrote:

We have files which contain management information, alway have the same
names and art stored in a known location on the intranet - eg

http://thepulse/_uklife/_services/_m.../SBB%20MMM.xls

Is there any way I can get Excel to find out the last saved date of
these files, so I can compare with known saved dates and thus automate
the updating of local reports IF the data has changed?

does ftp with the same address work?

ftp://URL

does it require a username and password?

--
Regards,
Tom Ogilvy


" wrote:

Thanks for the help Jim, and I agree that what you have given me works
fine when the file path is local (c:\, or mapped g:\ or whatever) BUT
my problem is that the file is saved on the intranet, the only known I
have at the moment is the http:\\ location - I guess in desperation I
could search the servers till I find the ACTUAL location, but I'm
HOPING to do it using the http:\\ location


Jim Thomlinson wrote:
Here is some code for you. You MUST reference "Microsoft Scripting Runtime"
to use this code. (Tools - References -...). Run the procedure called test.
It will search the directory specified (and all subdirectories if the final
argument is true) and populate sheet 1 with the file name, creation date and
last modified date...

Option Explicit
Option Compare Text

Sub test()
Call ListFiles("C:\Test", Sheet1.Range("A2"), "xls", True)
End Sub

Public Sub ListFiles(ByVal strPath As String, _
ByVal rngDestination As Range, Optional ByVal strFileType As String = "*", _
Optional ByVal blnSubDirectories As Boolean = False)
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strName As String

'Specify the file to look for...
strName = "*." & strFileType
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
rngDestination.Value = objFile.Path
rngDestination.Offset(0, 1).Value = objFile.DateLastAccessed
rngDestination.Offset(0, 2).Value = objFile.DateLastModified
Set rngDestination = rngDestination.Offset(1, 0)
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
If blnSubDirectories = True Then _
DoTheSubFolders objFolder.SubFolders, rngDestination, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub


Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef rng As Range, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

On Error GoTo ErrorHandler
For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
rng.Value = scrFile.Path
rng.Offset(0, 1).Value = scrFile.DateLastAccessed
Set rng = rng.Offset(1, 0)
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, rng, strTitle
End If
ErrorHandler:
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'-------------------

--
HTH...

Jim Thomlinson


" wrote:

We have files which contain management information, alway have the same
names and art stored in a known location on the intranet - eg

http://thepulse/_uklife/_services/_m.../SBB%20MMM.xls

Is there any way I can get Excel to find out the last saved date of
these files, so I can compare with known saved dates and thus automate
the updating of local reports IF the data has changed?

We just follow the link quoted (actually, there are about 12 of them) -
using VBA in Excel I could open them and get the details that way, but
I'd like to do it without opening them - I guess FTP could work but not
sure if it's enabled on our machines - though don't know how I'd use
that programatically?


Tom Ogilvy wrote:
does ftp with the same address work?

ftp://URL

does it require a username and password?

--
Regards,
Tom Ogilvy


" wrote:

Thanks for the help Jim, and I agree that what you have given me works
fine when the file path is local (c:\, or mapped g:\ or whatever) BUT
my problem is that the file is saved on the intranet, the only known I
have at the moment is the http:\\ location - I guess in desperation I
could search the servers till I find the ACTUAL location, but I'm
HOPING to do it using the http:\\ location


Jim Thomlinson wrote:
Here is some code for you. You MUST reference "Microsoft Scripting Runtime"
to use this code. (Tools - References -...). Run the procedure called test.
It will search the directory specified (and all subdirectories if the final
argument is true) and populate sheet 1 with the file name, creation date and
last modified date...

Option Explicit
Option Compare Text

Sub test()
Call ListFiles("C:\Test", Sheet1.Range("A2"), "xls", True)
End Sub

Public Sub ListFiles(ByVal strPath As String, _
ByVal rngDestination As Range, Optional ByVal strFileType As String = "*", _
Optional ByVal blnSubDirectories As Boolean = False)
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strName As String

'Specify the file to look for...
strName = "*." & strFileType
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
rngDestination.Value = objFile.Path
rngDestination.Offset(0, 1).Value = objFile.DateLastAccessed
rngDestination.Offset(0, 2).Value = objFile.DateLastModified
Set rngDestination = rngDestination.Offset(1, 0)
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
If blnSubDirectories = True Then _
DoTheSubFolders objFolder.SubFolders, rngDestination, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub


Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef rng As Range, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

On Error GoTo ErrorHandler
For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
rng.Value = scrFile.Path
rng.Offset(0, 1).Value = scrFile.DateLastAccessed
Set rng = rng.Offset(1, 0)
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, rng, strTitle
End If
ErrorHandler:
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'-------------------

--
HTH...

Jim Thomlinson


" wrote:

We have files which contain management information, alway have the same
names and art stored in a known location on the intranet - eg

http://thepulse/_uklife/_services/_m.../SBB%20MMM.xls

Is there any way I can get Excel to find out the last saved date of
these files, so I can compare with known saved dates and thus automate
the updating of local reports IF the data has changed?