VBA Create Linked Cell to Another Workbook

[crussom1]

Ok, so this is something which is probably so simple but I can't figure it out. I need a macro to create a linked cell to another workbook. The name of the workbook will change every time. The other workbook gets assigned a name based on a value they type into a form at the beginning, which is then stored as a variable (ProjectNum). I just want it to display the value of a specific cell on that other workbook as a linked cell (the value will change periodically and this file needs to be able to see updates).

So the macro recorder gave me this when I did a test run:

    ActiveCell.FormulaR1C1 = "='[Visboard Template Questions.xlsm]New'!R2C17"

But that uses a static name for the workbook so I tried splitting it up using & and inserting the variable name where the workbook should go like this but it didn't work (the real workbook name won't have any spaces)

ActiveCell.FormulaR1C1 = "=[" & ProjectNum & ".xlsm]" & ProjectNum & "!R2C17"

The closest I've gotten is this (it inserts the complete file path ProjectDir and the file name which is a variable), but it opens a file dialog and called Update Values and makes me select the file (if I do, it works, if not, it doesn't). I don't want to have to select the file though.

ActiveCell.Value = "='" & ProjectDir & "[" & ProjectNum & "]" & ProjectNum & "'!Q2"

Any help you could provide would be much appreciated

 

[AlphaFrog]

Something like this should work...

ActiveCell.[B]Formula [/B]= "='" & ProjectDir & "[" & ProjectNum & ".xlsx]" & ProjectSheetName & "'!Q2"

The workbook path (ProjectDir), workbook file name (ProjectNum), and the sheet name (ProjectSheetName) are three different strings.

 

[crussom1]

Something like this should work...

ActiveCell.[B]Formula [/B]= "='" & ProjectDir & "[" & ProjectNum & ".xlsx]" & ProjectSheetName & "'!Q2"

The workbook path (ProjectDir), workbook file name (ProjectNum), and the sheet name (ProjectSheetName) are three different strings.

Thanks, I'm so frustrated because I actually had it at some point then but I think I forgot the single quotes. Thank you for your help. This worked: (ProjectFile is a variable that has the file name ProjectNum & ".xlsm")

ActiveCell.FormulaR1C1 = "='" & ProjectFile & "'!R2C17"