Get FileSummaryInfo for files saved on Intranet
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? |
Get FileSummaryInfo for files saved on Intranet
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? |
Get FileSummaryInfo for files saved on Intranet
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? |
Get FileSummaryInfo for files saved on Intranet
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? |
Get FileSummaryInfo for files saved on Intranet
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? |
All times are GMT +1. The time now is 08:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com