CODEWITHSUNDEEP
excelvbavariablesmatchformula
Simone Evans
Simone Evans

Reputation: 311

Referencing File Path variable in Index/Match formula from different workbook with Excel VBA

Good day,

I'm trying to reference a file path variable in Index/Match formula with Excel VBA. Everything works up to the Index/Match formula and I think the file path and worksheet reference is the problem. Currently, it's giving the error "Run-time Error '1004': Application-defined or object-defined error"

This is just a section of the code since a lot happens with this macro and I have to do the same thing for 8 different files, with different names and different sheet names. Different people will use the macro, and the main files will be in different folders, therefore I have to add the variables for the file paths and worksheets.

I've read many posts trying to find a solution. A lot suggests using the indirect or indirect.ext function/formula but apparently with indirect.ext you have to enable an add-on, which I can't do since many different people will use the macro. And the indirect is apparently volatile. Since this formula will be used in at least 8 different columns and over 50 000 lines, this doesn't seem like a very good idea for processing time.

The external workbooks will be opened with code before this formula runs.

This is the section of code for the Index/Match formula.

Dim path As String
Dim StoreFile As String
Dim StoreFileF As String

path = GetFolder()

StoreFile = Dir(path & "\*Store and Format*.xls*")
StoreFileF = path & "\" & StoreFile

With ws.ListObjects("Table_SDCdata")
    With .ListColumns.Add
        .Name = "Region"
    End With
    .ListColumns("Region").DataBodyRange.Formula = "=INDEX('[" & StoreFileF & "]Worksheet[1]'!F:F,MATCH([@Site],'[" & StoreFileF & "]Worksheet[1]'!A:A)0,1)"
End With

Any suggested or help will be appreciated.

Upvotes: 1

Views: 2158

Answers (1)

CDP1802
CDP1802

Reputation: 16392

Square bracket the filename only

    path = "c:\path\to\file"
    file = "My Workbook.xlsx"
    sheetname = "Sheet Name"

    ref = path & "\[" & file & "]" & sheetname
    sFormula = "=INDEX('" & ref & "'!$F:$F,MATCH([@Site],'" & ref & "'!$A:$A,0),1)"
    Debug.Print sFormula

    With ws.ListObjects("Table_SDCdata")
    With .ListColumns.Add
        .Name = "Region"
    End With
        .ListColumns("Region").DataBodyRange.Formula = sFormula
    End With

Upvotes: 3

Related Questions