Hi all,

I am a Excel & VBA newbie and I have to do this kind of script :

in column A, I have a liste of reference. Example : "R-AD-H-F"
in a directory, call it '\myDir\', I have many subdirectories whith a lot
of files. File format is, for example, "R AD H F.APJ"

= I need to put in column B a list of link to file in '\myDir\' for each
reference in column A

Any ideas ?
Thanks.

Best regards,

Matthieu.

Folder = "\myDir"
Extension = "apj"
RowCount = 1
Do While Range("A" & RowCount) < ""
MyFile = Folder & "\" & Range("A" & RowCount) & _
"." & "Extension"
Range("B" & RowCount).Hyperlinks.Add _
Anchor:=Selection, _
Address:=MyFile, _
TextToDisplay:=MyFile
RowCount = RowCount + 1
Loop

"Matthieu B." wrote:

Hi all,

I am a Excel & VBA newbie and I have to do this kind of script :

in column A, I have a liste of reference. Example : "R-AD-H-F"
in a directory, call it '\myDir\', I have many subdirectories whith a lot
of files. File format is, for example, "R AD H F.APJ"

= I need to put in column B a list of link to file in '\myDir\' for each
reference in column A

Any ideas ?
Thanks.

Best regards,

Matthieu.

Hi Joel, and thank you for your awnser !

Its a good begining, but what I have to do is a bit more difficult.

Excuse me for my misexplanation.

My files are not all in the directory 'MyDir'.
There are a lot of subdirectories, with various names convention.

I need a function that recursively find the file location with a filter
function.
Exemple : cell A1 contains "A-ZER-TY"
the file to link to is in
\MyDir\projetct\projectblablabla\AZERTY.APJ
I need to put a link in B1 to this file
And so from A1 to Ax.

But I like to thank you a lot for your awnser.
It's a nice begining for me and I will try to work on it to improve your
function.

If anyone have others suggestions, you are welcome !

Best regards,

Matthieu

It took me a couple of minutes to modify another macro I had. the code
recursively goes through all subdirectories and then make the link.

Sub GetLinks()
'
' Macro1 Macro
' Macro recorded 3/2/2008 by Joel
'

'
strFolder = '"\myDir\" 'make sure you leave last backslash
Extension = "apj"
RowCount = 1
Do While Range("A" & RowCount) < ""
location = ""
MyFile = Range("A" & RowCount) & "." & Extension
Call GetSubFolderSize(strFolder, MyFile, location)
if location < "" then
MyFile = location & Range("A" & RowCount) & _
"." & Extension
Range("B" & RowCount).Hyperlinks.Add _
Anchor:=Range("B" & RowCount), _
Address:=MyFile, _
TextToDisplay:=MyFile
end if
RowCount = RowCount + 1
Loop
End Sub
Sub GetSubFolderSize(strFolder, MyFile, ByRef location)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetSubFolderSize(strFolder + sf.Name + "\", _
MyFile, location)
If location < "" Then Exit For
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If location = "" Then
If Not folder.isrootfolder Then
FName = Dir(strFolder & MyFile)
If FName < "" Then
location = strFolder
End If
End If
End If

200 On Error GoTo 0

End Sub


"Matthieu B." wrote:


Hi Joel, and thank you for your awnser !

Its a good begining, but what I have to do is a bit more difficult.

Excuse me for my misexplanation.

My files are not all in the directory 'MyDir'.
There are a lot of subdirectories, with various names convention.

I need a function that recursively find the file location with a filter
function.
Exemple : cell A1 contains "A-ZER-TY"
the file to link to is in
\MyDir\projetct\projectblablabla\AZERTY.APJ
I need to put a link in B1 to this file
And so from A1 to Ax.

But I like to thank you a lot for your awnser.
It's a nice begining for me and I will try to work on it to improve your
function.

If anyone have others suggestions, you are welcome !

Best regards,

Matthieu

Hi Joel,

I don't know how to thank you Joel for this job.
Thank you very much for taking time to modify your maccro.

Your script works great !

Thank you very very much.

You are outstanding.

Best regards,

Matthieu