link in excel formula

[bultimatem]

I am having a problem inserting a filepath in a formula. There is some really nasty splitting going on.

In VBA I am inserting a formula in a cell in the form:
=someStuff, someotherstuff,LOOKUP(range1,[C:\somefolder\someWorkbook.xls]someSheet!Range2,[C:\somefolder\someWorkbook.xls]someSheet!Range3)

In VBA for the file path name, I'm using Application.Workbooks(someWorkbook.Name).FullName

so my code looks like:
Dim filePath as string
filePath = Application.Workbooks(neededWorkbook.Name).FullName
Dim theSheet as worksheet
set theSheet = neededWorkbook.Sheets("someSheet&quot
Dim Rg2 as range, Rg3 as range
set Rg2 = theSheet.Range("Range2&quot
set Rg3 = theSheet.Range("Range3&quot

Dim theFormula as string

theFormula = "=somestuff, someotherstuff, LOOKUP(range1," _
" '[" & filePath & "]" & theSheet.Name & "'!" & Rg2.Name ...

the problem is that the actual formula entered in the activecell is:

= somestuff, someotherstuff, LOOKUP(range1, 'C:\somefolder [C:\somefolder\someWorkbook.xls]someSheet'!Range3 ...

I have no idea why it's splitting up that file path like that. I realize that there needs to be single quotes because there is the possibility of being folders with embedded spaces. This is the only set up of the single quotes that doesn't give me an error. I've read that there often needs to be two single quotes at the beginning (''[C:\..]sheet'!range) but I tried that and I received an error every time it tried to enter it in the cell.

Any ideas? Thanks in advance


Bryan Marble
IEWS
BAE Systems

 

[bultimatem]

Just as added info, I need to choose the sheet because the range isn't specific to one sheet.

Bryan Marble
IEWS
BAE Systems