go to the same path of the excel file being launched

[Guest]

Hi All,
This macro:

Sub GetData()
With Range("F1:F10")
.Formula = "='C:\Documents and
Settings\Tim\Desktop\test\[Book1.xls]Sheet1'!A1"
.Formula = .Value
End With
End Sub

works fine but i need to change the line:

..Formula = "='C:\Documents and
Settings\Tim\Desktop\test\[Book1.xls]Sheet1'!A1"

Instead to go to that directory above, to go to the folder test which is in
the same path of the excel file being launched.
Something like this:
..Formula =ThisWorkbook.Path & "\test\Book1.xls"

Any Help is highly appreciated.

Tim

 

[Guest]

In my previous posting i mean i need to change the
line:
..Formula = "='C:\Documents and
Settings\Tim\Desktop\test\[Book1.xls]Sheet1'!A1"

to something like this:
..Formula =ThisWorkbook.Path & "\test\[Book1.xls]Sheet1'!A1"

As you can guess the second line is only guided. It doesn’t work and i need
your help to fix it.

Tim

 

[Vasant Nanavati]

Try something like:

..Formula = "=" & ThisWorkbook.Path & "\test\[Book1.xls]Sheet1'!A1"

In my previous posting i mean i need to change the
line:
.Formula = "='C:\Documents and
Settings\Tim\Desktop\test\[Book1.xls]Sheet1'!A1"

to something like this:
.Formula =ThisWorkbook.Path & "\test\[Book1.xls]Sheet1'!A1"

As you can guess the second line is only guided. It doesn't work and i
need
your help to fix it.

Tim

Hi All,
This macro:

Sub GetData()
With Range("F1:F10")
.Formula = "='C:\Documents and
Settings\Tim\Desktop\test\[Book1.xls]Sheet1'!A1"
.Formula = .Value
End With
End Sub

works fine but i need to change the line:

.Formula = "='C:\Documents and
Settings\Tim\Desktop\test\[Book1.xls]Sheet1'!A1"

Instead to go to that directory above, to go to the folder test which is
in
the same path of the excel file being launched.
Something like this:
.Formula =ThisWorkbook.Path & "\test\Book1.xls"

Any Help is highly appreciated.

Tim