any solution to slash problem

[driller]

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.

 

[Jacob Skaria]

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

 

[driller]

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]

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

 

[driller]

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]

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

 

[driller]

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.

 

[driller]

Jacob,

 

[Jacob Skaria]

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]

---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

 

[driller]

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]

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

 

[driller]

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]

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

 

[driller]

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 ?

 

[Jacob Skaria]

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

 

[driller]

thaks a lot, you made a great help.

\--
regards
driller