collect data automatically from a folder

[aya2002]

Dear Friends,

I have just signed here, so I am very new here.

I need your help for my problem:

suppose that i have a folder contains more than 200 text files (.txt
or may be other extension say .jpg, any way, I need a program to extrac
the names of these files and list them into an Excel sheet and set
hyperlink to these files, so that when i click any one in the Exce
sheet it will be opened automatically.

Thanks

 

[Simon Lloyd]

This should do what you need


VBA Code:
--------------------


Sub Main(
Dim F As String, i As Integer, n As Integer, wks As Workshee
'Initializ
i =
Set wks = ActiveWorkbook.Worksheets.Add 'dummy worksheet to hold the file lis
ActiveSheet.Name = "Index
wks.Cells(i, 1).Value =
'Get the first filename that matches the patter
F = Dir("C:\*.xls", vbNormal
Do While F <> "" 'loop through all the file
'store the filename in a shee
wks.Cells(i, 1).Value =
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="C:\" & F, TextToDisplay:=
ActiveCell.Offset(1, 0).Selec
i = i +
F = Dir 'get the next filenam
Loo
n = i - 1 'n is the number of files foun
MsgBox "there were " & n & " Files Found
'sort the list of file
Columns("A:A").Selec
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNorma
Range("A1").Selec

ActiveWorkbook.Save 'As Filename:=

--------------------






Dear Friends

I have just signed here, so I am very new here

I need your help for my problem

suppose that i have a folder contains more than 200 text files (.txt
or may be other extension say .jpg, any way, I need a program to extrac
the names of these files and list them into an Excel sheet and set
hyperlink to these files, so that when i click any one in the Exce
sheet it will be opened automatically

Thanks


--
Simon Lloyd

Regards
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)

 

[aya2002]

the code was powerful, but when i click on any file it can't be open,
got this message "can't open the specified file" also the tool tip tex
refers to another location on the computer ! how come?

 

[Simon Lloyd]

Thats because you didn't adapt the code for your use! take a look a
these lines


VBA Code:
--------------------


F = Dir("C:\*.xls", vbNormal)
--------------------





VBA Code:
--------------------


ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="C:\" & F, TextToDisplay:=F
--------------------


Notice the "C:\" you need to change both to the same location so i
your drive is W they should read "W:\"


--
Simon Lloyd

Regards
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)

 

[Project Mangler]

Code works great once I'd added a path to my home folder.

I got the same error as aya2002 on trying to open some of the files.

I now know that you can't have a # symbol in the filename in a MS Offive
hyperlink:

http://support.microsoft.com/kb/202261

Cheers Simon.

DB

 

[aya2002]

Hi,

I have modified the code and it is working right now very good:

Sub Button4_Click()
Dim F As String, i As Integer, n As Integer, wks As Worksheet
'Initialize
i = 1
' Set wks = ActiveWorkbook.Worksheets.Add 'dummy worksheet to hol
the file list
' ActiveSheet.Name = "Index"
ActiveSheet.Cells(i, 1).Value = F
'Get the first filename that matches the pattern
F = Dir("K:\New Folder\Electronics\microwaves 3\*.*", vbNormal)
Do While F <> "" 'loop through all the files
'store the filename in a sheet
ActiveSheet.Cells(i, 1).Value = F
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="K:\Ne
Folder\Electronics\microwaves 3\" & F, TextToDisplay:=F
ActiveCell.Offset(1, 0).Select
i = i + 1
F = Dir 'get the next filename
Loop
n = i - 1 'n is the number of files found
MsgBox "there were " & n & " Files Found"
'sort the list of files

ActiveWorkbook.Save 'As Filename:=F

End Sub

 

[Simon Lloyd]

Glad we could be of help!



Hi

I have modified the code and it is working right now very good also,
made it to collect the data from the current path where your workbook i
saved right now

VBA Code:
--------------------
Sub Button4_Click(
Dim F As String, i As Integer, n As Integer, wks As Workshee
'Initializ
i =
' Set wks = ActiveWorkbook.Worksheets.Add 'dummy worksheet to hold the file lis
' ActiveSheet.Name = "Index
ActiveSheet.Cells(i, 1).Value =
'Get the first filename that matches the patter
F = Dir("K:\New Folder\Electronics\microwaves 3\*.*", vbNormal
Do While F <> "" 'loop through all the file
'store the filename in a shee
ActiveSheet.Cells(i, 1).Value =
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="K:\New Folder\Electronics\microwaves 3\" & F, TextToDisplay:=
ActiveCell.Offset(1, 0).Selec
i = i +
F = Dir 'get the next filenam
Loo
n = i - 1 'n is the number of files foun
MsgBox "there were " & n & " Files Found
'sort the list of file

ActiveWorkbook.Save 'As Filename:=

End Su

--------------------



Glad we could be of help!


--
Simon Lloyd

Regards
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)