|
any solution to slash problem
Hello,
i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
Replace
Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
Jacob,
cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
Modified the procedure
Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = strFolder & folder.Name & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
here now is what i have, i am not a programmer thats why the code takes time
for for me to understand. --- sub getdates() Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --- "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
It should have been folder.Path and not folder.Name. Try the below
Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
Jacob,
it seems to work like this A1 = "d:\temp" click macro...getdates() A2="d:\temp\Book1.xls" A3="d:\temp\Book2.xls" A4~A1000= "d:\temp\Booked\.jpg" but all the hyperlink function were gone and the "d:\temp" were all displayed from A2~A1000. "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
Jacob,
--- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
How about this one...which will clear the contents of 3 columns and assign
hyperlink. Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) ws.Range("A:C").ClearContents RowNumber = 1 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A").Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" RowNumber = RowNumber + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, it seems to work like this A1 = "d:\temp" click macro...getdates() A2="d:\temp\Book1.xls" A3="d:\temp\Book2.xls" A4~A1000= "d:\temp\Booked\.jpg" but all the hyperlink function were gone and the "d:\temp" were all displayed from A2~A1000. "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
---can this line be adjusted someway like this
Yes you can. Please try the below one... Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow) = fl.DateLastModified ws.Range("A" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, --- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
almost there, just one more shot.
can it be possible to replace "d:\temp\" into "use Text from cell D1"...somekindof your generic coding ? also, can Column A display only the pure filename ("book1.xls" ; "booked.xls"), not necessarily the complete hyperlink address ? "Jacob Skaria" wrote: How about this one...which will clear the contents of 3 columns and assign hyperlink. Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) ws.Range("A:C").ClearContents RowNumber = 1 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A").Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" RowNumber = RowNumber + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, it seems to work like this A1 = "d:\temp" click macro...getdates() A2="d:\temp\Book1.xls" A3="d:\temp\Book2.xls" A4~A1000= "d:\temp\Booked\.jpg" but all the hyperlink function were gone and the "d:\temp" were all displayed from A2~A1000. "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
also, can Column A display only the pure filename ("book1.xls" ;
"booked.xls"), not necessarily the complete hyperlink address ? Please find the below version...which will take the path from cell A1 of sheet(1) and would show the file name alone Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("C" & lngRow) = fl.DateLastModified ws.Range("B" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: ---can this line be adjusted someway like this Yes you can. Please try the below one... Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow) = fl.DateLastModified ws.Range("A" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, --- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
You're a genuine.
Can I run this code for the 30 sheets of the workbook ? It seems to work for Sheet1 only. Each sheet will contain a different folder address "text" in A1. Meaning I can place all the filenames in 30 sheets. is it still possible? "Jacob Skaria" wrote: also, can Column A display only the pure filename ("book1.xls" ; "booked.xls"), not necessarily the complete hyperlink address ? Please find the below version...which will take the path from cell A1 of sheet(1) and would show the file name alone Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("C" & lngRow) = fl.DateLastModified ws.Range("B" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: ---can this line be adjusted someway like this Yes you can. Please try the below one... Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow) = fl.DateLastModified ws.Range("A" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, --- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
You can..try the below...
Sub GetFileDetails() Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set fso = CreateObject("Scripting.FileSystemObject") For Each ws In Worksheets ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" ws.Range("B" & lngRow) = fl.Size ws.Range("C" & lngRow) = fl.DateLastModified lngRow = lngRow + 1 Next End If Next End Sub PS:Suggest you to put together all your requests in your initial post itself which will help us in suggesting a better solution in the first place itself. If this post helps click Yes --------------- Jacob Skaria "driller" wrote: You're a genuine. Can I run this code for the 30 sheets of the workbook ? It seems to work for Sheet1 only. Each sheet will contain a different folder address "text" in A1. Meaning I can place all the filenames in 30 sheets. is it still possible? "Jacob Skaria" wrote: also, can Column A display only the pure filename ("book1.xls" ; "booked.xls"), not necessarily the complete hyperlink address ? Please find the below version...which will take the path from cell A1 of sheet(1) and would show the file name alone Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("C" & lngRow) = fl.DateLastModified ws.Range("B" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: ---can this line be adjusted someway like this Yes you can. Please try the below one... Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow) = fl.DateLastModified ws.Range("A" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, --- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
ooops..
The 30 sheets automatically generates the filename & hyperlink so nice.. I tried few test and it opens the file until i stomped into a pop-up.. can we avoid this pop-up note ? do i need to make some re-settings ? ----- Opening "D:\temp\Book143.xls" Hyperlinks can be harmful to your computer and data. To protect your computer, click only those hyperlinks from trusted sources. Do you want to continue? ----- thank you -- regards "Jacob Skaria" wrote: You can..try the below... Sub GetFileDetails() Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set fso = CreateObject("Scripting.FileSystemObject") For Each ws In Worksheets ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" ws.Range("B" & lngRow) = fl.Size ws.Range("C" & lngRow) = fl.DateLastModified lngRow = lngRow + 1 Next End If Next End Sub PS:Suggest you to put together all your requests in your initial post itself which will help us in suggesting a better solution in the first place itself. If this post helps click Yes --------------- Jacob Skaria "driller" wrote: You're a genuine. Can I run this code for the 30 sheets of the workbook ? It seems to work for Sheet1 only. Each sheet will contain a different folder address "text" in A1. Meaning I can place all the filenames in 30 sheets. is it still possible? "Jacob Skaria" wrote: also, can Column A display only the pure filename ("book1.xls" ; "booked.xls"), not necessarily the complete hyperlink address ? Please find the below version...which will take the path from cell A1 of sheet(1) and would show the file name alone Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("C" & lngRow) = fl.DateLastModified ws.Range("B" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: ---can this line be adjusted someway like this Yes you can. Please try the below one... Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow) = fl.DateLastModified ws.Range("A" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, --- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
Check out the below link on how to disable hyperlink warning messages in
Office 2003 http://support.microsoft.com/kb/829072/en-us If this post helps click Yes --------------- Jacob Skaria "driller" wrote: ooops.. The 30 sheets automatically generates the filename & hyperlink so nice.. I tried few test and it opens the file until i stomped into a pop-up.. can we avoid this pop-up note ? do i need to make some re-settings ? ----- Opening "D:\temp\Book143.xls" Hyperlinks can be harmful to your computer and data. To protect your computer, click only those hyperlinks from trusted sources. Do you want to continue? ----- thank you -- regards "Jacob Skaria" wrote: You can..try the below... Sub GetFileDetails() Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set fso = CreateObject("Scripting.FileSystemObject") For Each ws In Worksheets ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" ws.Range("B" & lngRow) = fl.Size ws.Range("C" & lngRow) = fl.DateLastModified lngRow = lngRow + 1 Next End If Next End Sub PS:Suggest you to put together all your requests in your initial post itself which will help us in suggesting a better solution in the first place itself. If this post helps click Yes --------------- Jacob Skaria "driller" wrote: You're a genuine. Can I run this code for the 30 sheets of the workbook ? It seems to work for Sheet1 only. Each sheet will contain a different folder address "text" in A1. Meaning I can place all the filenames in 30 sheets. is it still possible? "Jacob Skaria" wrote: also, can Column A display only the pure filename ("book1.xls" ; "booked.xls"), not necessarily the complete hyperlink address ? Please find the below version...which will take the path from cell A1 of sheet(1) and would show the file name alone Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("C" & lngRow) = fl.DateLastModified ws.Range("B" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: ---can this line be adjusted someway like this Yes you can. Please try the below one... Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow) = fl.DateLastModified ws.Range("A" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, --- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
any solution to slash problem
thaks a lot, you made a great help.
\-- regards driller "Jacob Skaria" wrote: Check out the below link on how to disable hyperlink warning messages in Office 2003 http://support.microsoft.com/kb/829072/en-us If this post helps click Yes --------------- Jacob Skaria "driller" wrote: ooops.. The 30 sheets automatically generates the filename & hyperlink so nice.. I tried few test and it opens the file until i stomped into a pop-up.. can we avoid this pop-up note ? do i need to make some re-settings ? ----- Opening "D:\temp\Book143.xls" Hyperlinks can be harmful to your computer and data. To protect your computer, click only those hyperlinks from trusted sources. Do you want to continue? ----- thank you -- regards "Jacob Skaria" wrote: You can..try the below... Sub GetFileDetails() Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set fso = CreateObject("Scripting.FileSystemObject") For Each ws In Worksheets ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" ws.Range("B" & lngRow) = fl.Size ws.Range("C" & lngRow) = fl.DateLastModified lngRow = lngRow + 1 Next End If Next End Sub PS:Suggest you to put together all your requests in your initial post itself which will help us in suggesting a better solution in the first place itself. If this post helps click Yes --------------- Jacob Skaria "driller" wrote: You're a genuine. Can I run this code for the 30 sheets of the workbook ? It seems to work for Sheet1 only. Each sheet will contain a different folder address "text" in A1. Meaning I can place all the filenames in 30 sheets. is it still possible? "Jacob Skaria" wrote: also, can Column A display only the pure filename ("book1.xls" ; "booked.xls"), not necessarily the complete hyperlink address ? Please find the below version...which will take the path from cell A1 of sheet(1) and would show the file name alone Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("C" & lngRow) = fl.DateLastModified ws.Range("B" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """,""" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: ---can this line be adjusted someway like this Yes you can. Please try the below one... Sub GetFileDetails() 'Declarations Dim fso As Object, folder As Object Dim lngRow As Long, ws As Worksheet Set ws = Sheets(1) 'Clear the existing range ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _ "A").End(xlUp).Row).ClearContents Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(ws.Range("A1")) Then Set folder = fso.GetFolder(ws.Range("A1")) Else MsgBox "Folder could not be found": Exit Sub End If lngRow = 2 For Each fl In folder.Files ws.Range("A" & lngRow) = fl.DateLastModified ws.Range("A" & lngRow) = fl.Size ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _ folder.Path & "\" & fl.Name & """)" lngRow = lngRow + 1 Next End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, --- strFolder = "d:\temp\" --- can this line be adjusted someway like this --- strFolder = "use text from cell A1" --- "Jacob Skaria" wrote: It should have been folder.Path and not folder.Name. Try the below Sub getdates() Dim strFolder As String, fso As Object, folder As Object Dim RowNumber As Long, ws As Worksheet strFolder = "d:\temp\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(strFolder) Set ws = Sheets(1) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In folder.Files ws.Cells(RowNumber, "C") = fl.DateLastModified ws.Cells(RowNumber, "B") = fl.Size ws.Cells(RowNumber, "A") = folder.Path & "\" & fl.Name RowNumber = RowNumber + 1 Next 200 On Error GoTo 0 End Sub If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Jacob, cool, yet the hyperlink result is the same with missing slash "\"... well currently in A1 = "c:\temp" ; will it be possible to use this sheet with this modified code wherein A1 can be a generic folder address. (e.g "c:\temp1" or "c:\temp2", etc.) thanks "Jacob Skaria" wrote: Replace Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name with Sheets(1).Cells(RowNumber, "A") = strFolder & "\" & fl.Name PS:However I dont see the variable strFolder being assigned....Hope you have on top of the posted code ..OR try Sheets(1).Cells(RowNumber, "A") = Folder.Name & "\" & fl.Name If this post helps click Yes --------------- Jacob Skaria "driller" wrote: Hello, i found some code that works so neat, i just need to reconcile one (1) thing. i like to have this code to provide the correct hyperlink addresses along Col A. ------- Sub getdates() Folder = "C:\temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set Folder = _ fso.GetFolder(Folder) RowNumber = 1 'folder size in bytes On Error GoTo 200 For Each fl In Folder.Files Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified Sheets(1).Cells(RowNumber, "B") = fl.Size Sheets(1).Cells(RowNumber, "A") = strFolder & fl.Name RowNumber = RowNumber + 1 Next fl 200 On Error GoTo 0 End Sub --------- e.g folder = c:\temp file inside = book1.xls thus i need in col A to have a hyperlink address something like c:\temp\book1.xls. the resulting hyperlink address i have is c:\tempbook1.xls thus pop-up says that "Cannot open the specified file" because of a missing "\" hope it is very simple for a column of filenames growing in a daily basis. -- regards |
| All times are GMT +1. The time now is 06:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com