|
Posted to microsoft.public.excel.misc
|
|
|
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
|