VBA Formula: Variable File Name and Variable Sheet Name

Question

I am trying to create a cell reference to a cell in another workbook. In my code below, I am using a variable for workbook name and sheet name.

SourceFileNamePath = Path and name of workbook I am linking to
SourceTab = Tab in the workbook I want to link to

Though the code runs fine, the formula generated is not working. Does anyone have any thoughts on whether I am referencing SourceFileNamePath and SourceTab correctly?

Code is below:

Cells(destStartRow, destStartCol).FormulaR1C1 = "='[" & SourceFileNamePath & "]" & SourceTab & "'!R" & sourceStartRow & "C" & sourceStartCol

Only the file name should be enclosed in brackets - e.g. ='C:\Temp\Data\[Book1.xlsx]Sheet2'!R5C10. (See this question for a possible way of setting it up.) — YowE3K

YowE3K YowE3K · Accepted Answer · 2017-09-13T01:14:58

The format to access a cell in a sheet in an external workbook is

'path\[filename]sheetname'!cell_reference

so if you have a variable called SourceFileNamePath containing the path and filename (e.g. "C:\Temp\Data\Book1.xlsx") then you need to separate the filename from the path.

You could use something like:

SourceFileNamePath = "C:\Temp\Data\Book1.xlsx"   ' or however you set that variable
SourceTab = "Sheet1"                             ' or however you set that variable

Dim SourceFilePath As String
Dim SourceFileName As String
SourceFilePath = Left(SourceFileNamePath, InStrRev(SourceFileNamePath, Application.PathSeparator))
SourceFileName = Mid(SourceFileNamePath, InStrRev(SourceFileNamePath, Application.PathSeparator) + 1)
Cells(destStartRow, destStartCol).FormulaR1C1 = "='" & SourceFilePath & "[" & SourceFileName & "]" & SourceTab & "'!R" & sourceStartRow & "C" & sourceStartCol

Note: If either the path or the filename contains any single-quotation marks (e.g. if the filename was Sukhbir's test file.xlsx) then it will need to be escaped (i.e. each single-quotation mark needs to be replaced by two single-quotation marks). This can be achieved by using the Replace function, e.g.:

Cells(destStartRow, destStartCol).FormulaR1C1 = _
                                 "='" & Replace(SourceFilePath, "'", "''") & _
                                 "[" & Replace(SourceFileName, "'", "''") & "]" & _
                                 SourceTab & "'!R" & sourceStartRow & "C" & sourceStartCol

VBA Formula: Variable File Name and Variable Sheet Name

1 Answers