collect data automatically from a folder
 

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 extract
the names of these files and list them into an Excel sheet and set a
hyperlink to these files, so that when i click any one in the Excel
sheet it will be opened automatically.

Thanks


--
aya2002
------------------------------------------------------------------------
aya2002's Profile: 1625
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187420

http://www.thecodecage.com/forumz/chat.php

collect data automatically from a folder
 

This should do what you need:


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


Sub Main()
Dim F As String, i As Integer, n As Integer, wks As Worksheet
'Initialize
i = 1
Set wks = ActiveWorkbook.Worksheets.Add 'dummy worksheet to hold the file list
ActiveSheet.Name = "Index"
wks.Cells(i, 1).Value = F
'Get the first filename that matches the pattern
F = Dir("C:\*.xls", vbNormal)
Do While F < "" 'loop through all the files
'store the filename in a sheet
wks.Cells(i, 1).Value = F
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="C:\" & 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
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select

ActiveWorkbook.Save 'As Filename:=F

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






aya2002;670935 Wrote:

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 extract
the names of these files and list them into an Excel sheet and set a
hyperlink to these files, so that when i click any one in the Excel
sheet it will be opened automatically.

Thanks


--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187420

http://www.thecodecage.com/forumz/chat.php

collect data automatically from a folder
 

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


--
aya2002
------------------------------------------------------------------------
aya2002's Profile: 1625
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187420

http://www.thecodecage.com/forumz/chat.php

collect data automatically from a folder
 

Thats because you didn't adapt the code for your use! take a look at
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 if
your drive is W they should read "W:\"


--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187420

http://www.thecodecage.com/forumz/chat.php

collect data automatically from a folder
 

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" wrote in message
...

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


--
aya2002
------------------------------------------------------------------------
aya2002's Profile: 1625
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=187420

http://www.thecodecage.com/forumz/chat.php

collect data automatically from a folder
 

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 hold
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:\New
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


--
aya2002
------------------------------------------------------------------------
aya2002's Profile: 1625
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187420

http://www.thecodecage.com/forumz/chat.php

collect data automatically from a folder
 

Glad we could be of help!



aya2002;671553 Wrote:

Hi,

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



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



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 hold 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:\New 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

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






Glad we could be of help!


--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187420

http://www.thecodecage.com/forumz/chat.php