CODEWITHSUNDEEP
vbaexcelexcel-formula
Sukhbir Gill
Sukhbir Gill

Reputation: 23

VBA Formula: Variable File Name and Variable Sheet Name

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.

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

Upvotes: 2

Views: 3257

Answers (1)

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 3

Related Questions