formula in vba

[KeythStone]

Hello all,

I am trying to use the CELL formula to refer to the filename of the workbook in VBA. I'm having an issue referring to the "filename" in the formula.

Range("A2").Formula = "=Mid(Cell("filename"),70,6)"

i get debug: on "filename" Expected end of statement. My guess is that it doesn't like the "" in the cell(). but when i take them out shown in the worksheet are only the values or i get #NAME?

the formula =Mid(cell("filename"),70,6) works fine in datasheet view.

Any help is much appreciate. Stay smooth,

KeythStone

 

[Joe4]

One way is to use Chr(34) to indicate your "literal" double-quote marks, i.e.

Range("A2").Formula = "=Mid(Cell(" & Chr(34) & "filename" & Chr(34) & "),70,6)"

 

[Majawat]

I think you could also replace the " inside of your formulas with "". Not sure why this works, but I've had your issue before. IIRC, it'd be:

Range("A2").Formula = "=Mid(Cell(""filename""),70,6)"

...I think.