CODEWITHSUNDEEP
vbaexcel
Calaom
Calaom

Reputation: 244

Put a formula inside a cell using an automatic path

With VBA I'm trying to put automatic paths to my resources inside Excel cells.
I've been trying to put a path from the directory where I launch my macro using:

ThisWorkbook.Path

This function works to delete a file as follows : 

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile ThisWorkbook.Path & "\File.xlsx", force

But I'm experiencing some trouble when trying to use it inside the following line:

Range("E2").Formula = "='" & ThisWorkbook.Path & "\[DJNDA.xls]Feuil1'!$I2"

Where I'm just wishing to obtain the value of a cell from a file in the same directory.
Right now the formula won't change if I move my file in another directory, that's why I want to use this kind of functions. I still use this kind of functions directly inside my cells:

='C:\path\to\file\[File.xls]Feuil1'!$I2

Thanks for your help

Upvotes: 1

Views: 229

Answers (1)

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Try the formula

= CELL("filename")

in your cell (after you have saved the workbook of course, so that a valid pathname exists for it).

Upvotes: 1

Related Questions