VBA filename into cell error

[Dani_LobP]

Hi,

I just created some macro that does a simply task.

Asks to open a CSV file, and then in that file adds 2 formulas.

Range("I10").Formula = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"
*I took this one from internet googling how to get filename without full path.

Range("H10").Formula = "=LEFT(I10,(FIND(""_"",I10,1)-1))"

The idea is to add the filename into a cell, and from that cell take just the start which is what I actually need.

My problem here, is that sometimes formula will work, and in other situations I get #VALUE! error...
When I check the error, I see the below in the formula 1 cell:

=MID(@CELL("filename"),SEARCH("[",@CELL("filename"))+1, SEARCH("]",@CELL("filename"))-SEARCH("[",@CELL("filename"))-1)

I try removing the @ symbols, but would still give error.
if i try =Cells(Filename) i will notice that there is no [] .. so probably error comes from there. But I have no clue in why sometimes filename will bring [] and why other times it won't...

Hope someone can help me out here or provide a workaround for the same purpose.

Thanks in advance.

​

 

[Dani_LobP]

for now fixed with workaround using activeworkbook.name to obtain in the cell the file name.... i guess it will work and basically do the same.