Insert file into formula

Help Studio
1

Hello everyone,

I have to insert the VLOOKUP formula to sum up specific columns from the newest file in a folder.

To get the newest file I used:
outfilepath=Directory.GetFiles(FolderPath,"*.xlsx”,SearchOption.AllDirectories).OrderByDescending(Function(d) New FileInfo(d).CreationTime)(0)

Then I wrote the formula into a txt file and replaced the field < C1>, C2 with the cells I need.
=IFERROR(VLOOKUP($B< C1>,‘< C3>’!$A:$N,< C2>$2,0),0)

I replaced C3 with outfilepath to insert the newest file path.

Until now the formula in excel looks like this:
=IFERROR(VLOOKUP($B4;‘C:\Users\admin\Box[Test file WK 35.xlsx]’!$A:$N;G$2;0);0)

But to get the data I need I, the formula also needs the sheetname (“output”)
But if I just insert the word “Output” right after the < C3> in the txt then the formula looks like this in Excel:

=IFERROR(VLOOKUP($B4;‘C:\Users\admin\Box[Test file WK 35.xlsxOutput]Test file WK 30’!$A:$N;G$2;0);0)

And that’s wrong.

Does anyone know how I can handle this problem?

2

In Excel the reference to a linked file should look like
‘[file_name.xlsx]sheet_name’!range

So in your case
‘[C:\Users\admin\BoxTest file WK 35.xlsx]Output’!$A:$N

Cheers

3

Hello @Loons ,
Add replace string < C4 > right after < C3 > in your formula in text file and replace this with sheet name that you want.

2 Likes
4

I tried this, but if I always get the error that the formula is incorrect.
Because the result looks like

=IFERROR(VLOOKUP($B4;‘[C:\Users\admin\BoxTest file WK 35.xlsx]'Output!$A:$N;G$2;0);0)

and the formula only works if the are between the file name only…not between the whole path… :confused:

1 Like
5

Okey.
In that case
1.Try to get full file path using Path.GetFullPath(file) method and store it in variable X.
2.Replace Path.GetFileName(file) with “[”+Path.GetFileName(file)+“]” in X variable

1 Like
6

Finally it works. Thank you very much! :slight_smile:

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.