Replace filename with variable in "Activecell.FormulaR1C1=" expression

[pommyb]

I am new to Excel VBA so excuse me if I am asking a silly question. I have recorded a macro which contains several lines of code like this one:

ActiveCell.FormulaR1C1 = "='[OCR 14-02-09.xls]sun (2)'!R38C4:R38C5"

I would like to replace the filename "OCR 14-02-09.xls" with a variable. I have tried several different combos of brackets and quotes but always get an error message. Is this possible?

Thanks
pommyb

[DonkeyOte]

pommyb,

first please edit your last post and adjust your closing CODE tag to [*/CODE*] without asterisks

to then answer your Q

Dim strFile As String
strFile = "OCT 14-02-09.xls"
ActiveCell.FormulaR1C1 = "='[" & strFile & "]sun (2)'!R38C4:R38C5"

Though this formula doesn't make much sense given you're referencing a 2 column range D38:E38 ... you should be looking to "do" something with it... SUM / COUNT etc...

[pommyb]

Donkeyote,

Thanks, your solution worked just fine. With regard to your last comment, I recorded that part of the macro.

Regards

pommyb

[Tomkat]

Does anyone know why THIS DOESN'T work?

Sub testst()
Dim strFile As String

strFile = Application.GetOpenFilename("Excel Files (*.xlsm), *.xlsm")

ActiveCell.FormulaR1C1 = "=COUNTA('[ &strFile& ](CV-Summary)'!$A:$A)"

End Sub

I just want the filename (closed file) that it counts cells from to be a variable... why is this so difficult?

[daddylonglegs]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.