SOLVED

Variable workbook name in a formula “indirect”

%3CLINGO-SUB%20id%3D%22lingo-sub-1330046%22%20slang%3D%22en-US%22%3EVariable%20workbook%20name%20in%20a%20formula%20%E2%80%9Cindirect%E2%80%9D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330046%22%20slang%3D%22en-US%22%3EDears%2C%3CBR%20%2F%3EI%20have%20been%20trying%20to%20find%20a%20solution%20to%20get%20some%20data%20from%20different%20excel%20workbooks%20without%20the%20need%20to%20have%20them%20opened.%20I%20tried%20the%20indirect%20function%2C%20but%20it%20doesn%E2%80%99t%20work.%20Also%2C%20index%20function%20can%E2%80%99t%20have%20a%20variable%20workbook%20name%20and%20must%20be%20in%20the%20formula.%20Is%20there%20any%20none%20VBA%20solution%20for%20this%20issue%3F%20And%20if%20i%20have%20to%20do%20a%20VBA%20code%2C%20would%20you%20help%20me%20extract%20around%2030%20cells%20from%20different%20files%20that%20change%20monthly%20and%20put%20them%20in%20specific%20cells%20in%20my%20current%20workbook%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1330046%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330390%22%20slang%3D%22en-US%22%3ERe%3A%20Variable%20workbook%20name%20in%20a%20formula%20%E2%80%9Cindirect%E2%80%9D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633236%22%20target%3D%22_blank%22%3E%40AldighaithirMM%3C%2FA%3E%26nbsp%3Bthere's%20no%20%22good%22%20way%20of%20doing%20this%20without%20VBA.%20Here%20is%20a%20helpful%20link%20to%20tell%20you%20about%20how%20to%20do%20this%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fgetting-values-from-a-closed-excel-workbook%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fgetting-values-from-a-closed-excel-workbook%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20some%20code%20to%20do%20what%20you%20want.%20Change%20the%20values%20to%20your%20book%20and%20sheet%20name%2C%20and%20range%20desired.%20Data%20will%20be%20pulled%20into%20an%20array%20(single%20value%20if%20a%20single%20cell%20passed).%20There%20are%20other%20methods%20but%20I've%20found%20this%20to%20be%20the%20fastest.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3EOption%20Explicit%0A%0A%0ASub%20Test_ExtractDataFromClosedFile()%0A%20%20%20%20Dim%20Values%20As%20Variant%0A%20%20%20%20Values%20%3D%20ExtractDataFromClosedFile(%22C%3A%5CUsers%5CZack%5CDesktop%5CTest.xlsx%22%2C%20%22Sheet1%22%2C%20%22A1%3AB10%22)%0A%20%20%20%20Stop%0AEnd%20Sub%0A%0A%0APublic%20Function%20ExtractDataFromClosedFile(%20_%0A%20%20%20%20ByVal%20FilePath%20As%20String%2C%20_%0A%20%20%20%20ByVal%20SheetName%20As%20String%2C%20_%0A%20%20%20%20ByVal%20RangeAddress%20As%20String%20_%0A%20%20%20%20)%20As%20Variant%0A%0A%20%20%20%20Dim%20Book%20As%20Workbook%0A%20%20%20%20Dim%20Sheet%20As%20Worksheet%0A%20%20%20%20Dim%20Range%20As%20Range%0A%20%20%20%20Dim%20BookOpen%20As%20Boolean%0A%20%20%20%20Dim%20BookName%20As%20String%0A%20%20%20%20Dim%20Values%20As%20Variant%0A%20%20%20%20%0A%20%20%20%20BookName%20%3D%20GetNameFromPath(FilePath)%0A%20%20%20%20%0A%20%20%20%20If%20IsWorkbookOpen(BookName)%20Then%0A%20%20%20%20%20%20%20%20BookOpen%20%3D%20True%0A%20%20%20%20%20%20%20%20Set%20Book%20%3D%20Workbooks(BookName)%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Set%20Book%20%3D%20Workbooks.Open(FilePath%2C%20False)%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20If%20WorksheetExists(SheetName%2C%20Book)%20Then%0A%20%20%20%20%20%20%20%20ExtractDataFromClosedFile%20%3D%20Book.Worksheets(SheetName).Range(RangeAddress).Value%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20If%20Not%20BookOpen%20Then%0A%20%20%20%20%20%20%20%20Book.Close%20False%0A%20%20%20%20End%20If%0A%0AEnd%20Function%0A%0A%0APublic%20Function%20GetNameFromPath(%20_%0A%20%20%20%20%20%20%20ByVal%20Path%20As%20String%20_%0A%20%20%20%20)%20As%20String%0A%0A%20%20%20%20Dim%20Position%20As%20Long%0A%0A%20%20%20%20Position%20%3D%20InStrRev(Path%2C%20GetPathSeparatorSafe)%0A%20%20%20%20If%20Position%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20GetNameFromPath%20%3D%20Mid(Path%2C%20Position%20%2B%201)%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20GetNameFromPath%20%3D%20Path%0A%20%20%20%20End%20If%0A%0AEnd%20Function%0A%0A%0APublic%20Function%20GetPathSeparatorSafe()%0A%0A%20%20%20%20Dim%20EnableEvents%20As%20Boolean%0A%0A%20%20%20%20EnableEvents%20%3D%20Application.EnableEvents%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20GetPathSeparatorSafe%20%3D%20Application.PathSeparator%0A%20%20%20%20Application.EnableEvents%20%3D%20EnableEvents%0A%0AEnd%20Function%0A%0A%0AFunction%20IsWorkbookOpen(%20_%0A%20%20%20%20%20%20%20%20%20ByVal%20WorkbookName%20As%20String%20_%0A%20%20%20%20%20%20%20%20%20)%20As%20Boolean%0A%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20IsWorkbookOpen%20%3D%20CBool(Len(Workbooks(WorkbookName).Name)%20%26lt%3B%26gt%3B%200)%0A%20%20%20%20On%20Error%20GoTo%200%0A%0AEnd%20Function%0A%0A%0AFunction%20WorksheetExists(%20_%0A%20%20%20%20%20%20%20%20%20ByVal%20SheetName%20As%20String%2C%20_%0A%20%20%20%20%20%20%20%20%20Optional%20TargetBook%20As%20Workbook%20_%0A%20%20%20%20%20%20%20%20%20)%20As%20Boolean%0A%0A%20%20%20%20If%20TargetBook%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20ActiveWorkbook%20Is%20Nothing%20Then%20Exit%20Function%0A%20%20%20%20%20%20%20%20Set%20TargetBook%20%3D%20ActiveWorkbook%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20WorksheetExists%20%3D%20CBool(Len(TargetBook.Worksheets(SheetName).Name)%20%26lt%3B%26gt%3B%200)%0A%20%20%20%20On%20Error%20GoTo%200%0A%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330585%22%20slang%3D%22en-US%22%3ERe%3A%20Variable%20workbook%20name%20in%20a%20formula%20%E2%80%9Cindirect%E2%80%9D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330585%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20Zack%20for%20the%20quick%20reply%3CBR%20%2F%3EWould%20you%20mind%20attaching%20an%20example%20file%20so%20i%20can%20relate%20to%20when%20trying%20to%20change%20the%20code%20to%20fit%20my%20requirements%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330644%22%20slang%3D%22en-US%22%3ERe%3A%20Variable%20workbook%20name%20in%20a%20formula%20%E2%80%9Cindirect%E2%80%9D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633236%22%20target%3D%22_blank%22%3E%40AldighaithirMM%3C%2FA%3E%26nbsp%3Bsure.%20Code%20is%20in%20'Extract%20Test.xlsm'.%20Values%20are%20from%20'Test.xlsx'.%20Change%20variables%20to%20suit.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
Dears,
I have been trying to find a solution to get some data from different excel workbooks without the need to have them opened. I tried the indirect function, but it doesn’t work. Also, index function can’t have a variable workbook name and must be in the formula. Is there any none VBA solution for this issue? And if i have to do a VBA code, would you help me extract around 30 cells from different files that change monthly and put them in specific cells in my current workbook?

Thank you
3 Replies
Highlighted

@AldighaithirMM there's no "good" way of doing this without VBA. Here is a helpful link to tell you about how to do this:

https://exceloffthegrid.com/getting-values-from-a-closed-excel-workbook/

 

Here is some code to do what you want. Change the values to your book and sheet name, and range desired. Data will be pulled into an array (single value if a single cell passed). There are other methods but I've found this to be the fastest.

Option Explicit


Sub Test_ExtractDataFromClosedFile()
    Dim Values As Variant
    Values = ExtractDataFromClosedFile("C:\Users\Zack\Desktop\Test.xlsx", "Sheet1", "A1:B10")
    Stop
End Sub


Public Function ExtractDataFromClosedFile( _
    ByVal FilePath As String, _
    ByVal SheetName As String, _
    ByVal RangeAddress As String _
    ) As Variant

    Dim Book As Workbook
    Dim Sheet As Worksheet
    Dim Range As Range
    Dim BookOpen As Boolean
    Dim BookName As String
    Dim Values As Variant
    
    BookName = GetNameFromPath(FilePath)
    
    If IsWorkbookOpen(BookName) Then
        BookOpen = True
        Set Book = Workbooks(BookName)
    Else
        Set Book = Workbooks.Open(FilePath, False)
    End If
    
    If WorksheetExists(SheetName, Book) Then
        ExtractDataFromClosedFile = Book.Worksheets(SheetName).Range(RangeAddress).Value
    End If
    
    If Not BookOpen Then
        Book.Close False
    End If

End Function


Public Function GetNameFromPath( _
       ByVal Path As String _
    ) As String

    Dim Position As Long

    Position = InStrRev(Path, GetPathSeparatorSafe)
    If Position > 0 Then
        GetNameFromPath = Mid(Path, Position + 1)
    Else
        GetNameFromPath = Path
    End If

End Function


Public Function GetPathSeparatorSafe()

    Dim EnableEvents As Boolean

    EnableEvents = Application.EnableEvents
    Application.EnableEvents = False
    GetPathSeparatorSafe = Application.PathSeparator
    Application.EnableEvents = EnableEvents

End Function


Function IsWorkbookOpen( _
         ByVal WorkbookName As String _
         ) As Boolean

    On Error Resume Next
    IsWorkbookOpen = CBool(Len(Workbooks(WorkbookName).Name) <> 0)
    On Error GoTo 0

End Function


Function WorksheetExists( _
         ByVal SheetName As String, _
         Optional TargetBook As Workbook _
         ) As Boolean

    If TargetBook Is Nothing Then
        If ActiveWorkbook Is Nothing Then Exit Function
        Set TargetBook = ActiveWorkbook
    End If
    On Error Resume Next
    WorksheetExists = CBool(Len(TargetBook.Worksheets(SheetName).Name) <> 0)
    On Error GoTo 0

End Function

 

HTH

Highlighted
Thank you very much Zack for the quick reply
Would you mind attaching an example file so i can relate to when trying to change the code to fit my requirements?

Highlighted
Best Response confirmed by AldighaithirMM (New Contributor)
Solution

@AldighaithirMM sure. Code is in 'Extract Test.xlsm'. Values are from 'Test.xlsx'. Change variables to suit.