Variable Workbook Name in Formula

Question

I am trying to write a code that takes a variable workbook name (declared earlier) and inputs it into a formula that will be pasted into a cell.

I have it to the point where the formula works if I just write the workbook in, and I have the variable declaration with the workbook path working okay, I just can't figure out the formatting to drop the variable into the formula code.

'''Spreadsheet opening and variable declaration

strFileToOpen = Application.GetOpenFilename _
(Title:="Select an updated Inventory Report", _
FileFilter:="Excel Files *.xls* (*.xls*),")

If strFileToOpen = False Then

    'Displaying a message if file not choosedn in the above step
    MsgBox "No file selected.", vbExclamation, "Sorry!"

    'And existing from the procedure
    Exit Sub
Else

End If

Workbooks.Open Filename:=strFileToOpen



Set InvRpt = ActiveWorkbook

InvRptName = ActiveWorkbook.FullName

Set InvSht = InvRpt.Worksheets("ALL")

'''Formula insert

ActiveCell.Formula = "=IF(ISERROR(GETPIVOTDATA(""Sec QTY Sum"", 'InvRptName'!$A$4,""Alias"",""" & y & """)),0,GETPIVOTDATA(""Sec QTY Sum"", 'InvRptName'!$A$4,""Alias"",""" & y & """))"

The end result would be for this code to work exactly like this, but with a variable instead of the workbook/sheet name:

 ActiveCell.Formula = "=IF(ISERROR(GETPIVOTDATA(""Sec QTY Sum"", '[8-14-19 AM INVENTORY.xls]Sheet2'!$A$4,""Alias"",""" & y & """)),0,GETPIVOTDATA(""Sec QTY Sum"", '[8-14-19 AM INVENTORY.xls]Sheet2'!$A$4,""Alias"",""" & y & """))"

Possible duplicate of [How can I insert variable into formula in VBA](https://stackoverflow.com/questions/42503316/how-can-i-insert-variable-into-formula-in-vba) — BigBen, Aug 14 '19 at 22:00

score 1 · Accepted Answer · answered Aug 15 '19 at 03:36

It always helps to place a Break Point into the line where you are having issues then use the Intermediate Window to figure out where it is going wrong. For example;

Place a Break Point at the last line where you have ActiveCell.Formula =
Run the code
In the Intermediate Window type ?"=IF(ISERROR(GETPIVOTDATA(""Sec QTY Sum"", 'InvRptName'!$A$4,""Alias"",""" & y & """)),0,GETPIVOTDATA(""Sec QTY Sum"", 'InvRptName'!$A$4,""Alias"",""" & y & """))"
Note; The ? instructs the Intermediate Window that you want to see a return value.
If it compiles ok you should see a resulting string, see if it looks as you expected. I'd guess your result will include the text 'InvRptName' where you are expecting '[8-14-19 AM INVENTORY.xls]Sheet2'.

To get '[8-14-19 AM INVENTORY.xls]Sheet2'!$A$4 try using;

?"'[" & ActiveWorkbook.Name & "]" & ActiveSheet.Name & "'!$A$4"

Another couple hot tips;

Use Debug.Print "add your messages here or a variable " & InvRptName within your code to output debug messages.
Use the Intermediate Widow to fix variables during execution InvRptName = "'[" & ActiveWorkbook.Name & "]" & ActiveSheet.Name & "'!$A$4" notice I excluded the ? this time.

Thank you! Separating it out into the workbook name and active sheet name to get the right formatting was exactly what I needed. — Luke P, Aug 20 '19 at 17:45

Variable Workbook Name in Formula

1 Answers1