Using VBA to set links in Excel 2002

[rustyshackleford]

Hi,

I'm trying to write some code that will:

A) Provide a list of all files in a directory
B) Link to a value on what is called the 'Input' sheet of each file.

I'm using the following code

Sub BPRLookup()

Dim Directory As String, file As String
Dim file_counter As Integer

file_counter = 1
Directory = "C:\Documents and Settings\rrobb\Desktop\BPR\"

'insert headers

Cells(file_counter, 1) = "Project"
Cells(file_counter, 2) = "Project Manager"
Range("A1:B1&quot.Font.Bold = True

'Get first file
file = Dir(Directory)
Do While file <> &quot;&quot;
file_counter = file_counter + 1
Cells(file_counter, 1) = file
Cells(file_counter, 2) = &quot;='[&quot; & Directory & file & &quot;]Input&quot; & &quot;'!$B$6&quot;

file = Dir
Loop
End Sub

The problem I'm having is that the value put into Cell(file_counter, 2) looks like this

='[C:\Documents and Settings\rrobb\Desktop\BPR\[1000-10.xls]Input]1000-10.xls]Input'!$B$6

I cannot figure out why it is inserting the project number (in this case 1000-10) and the Input sheet name twice. If I delete the Directory reference in the code, it does not do this, but then Excel cannot locate the correct file and I get an error there. Any help would be much appreciated.

Thanks,

Rhett

 

[rustyshackleford]

Figured it out. PAth nem needs to go outside the brakcets (though why that results in the particular error, I still don't understand). Damn, what a waste of a morning.

Rhett