Option Explicit
Option Private Module
'====================================================================================================
Sub Test()
Dim x, temp
If Not FILE_ChooseFile(temp) Then Exit Sub
If Not FILE_GetSheetsFromFile(temp) Then Exit Sub
    For Each x In temp
        Debug.Print "sub " & TypeName(x), x.Name
    Next x
End Sub
'====================================================================================================
'====================================================================================================
Function FILE_ChooseFile(temp) As Boolean
Dim x
x = Application.GetOpenFilename("Excel files(*.xls*),*.xls*", 1, "Выберите 1 файл Excel", , False)
If VarType(x) = vbBoolean Then Exit Function Else temp = x: FILE_ChooseFile = True
End Function
'====================================================================================================
Function FILE_GetArraysFromFile(ByVal txtFilePath$, arr1xRanges) As Boolean
Dim x, DA, EE, SU, i&

DA = Application.DisplayAlerts: Application.DisplayAlerts = False
EE = Application.EnableEvents: Application.EnableEvents = False
SU = Application.ScreenUpdating: Application.ScreenUpdating = False

Workbooks.Open txtFilePath, False, , , , , True, , , , , , , , True: i = -1
    For Each x In arr1xRanges
        i = i + 1: arr1xRanges(i) = Evaluate(x).Value2
    Next x
FILE_GetArraysFromFile = True

fin: ActiveWorkbook.Close False
Application.DisplayAlerts = DA
Application.EnableEvents = EE
Application.ScreenUpdating = SU
End Function
'====================================================================================================
Function FILE_GetSheetsFromFile(tmpFilePath) As Boolean
Dim x, sh As Worksheet, DA, EE, SU, i&

DA = Application.DisplayAlerts: Application.DisplayAlerts = False
EE = Application.EnableEvents: Application.EnableEvents = False
SU = Application.ScreenUpdating: Application.ScreenUpdating = False

Workbooks.Open tmpFilePath, False, , , , , True, , , , , , , , True
ReDim tmpFilePath(ActiveWorkbook.Worksheets.Count - 1): i = -1
    With CreateObject("Scripting.Dictionary")
        For Each sh In ActiveWorkbook.Worksheets
            x = .Item(sh)
        Next sh
        tmpFilePath = .Keys
    End With
    
    For Each x In tmpFilePath
        Debug.Print "func " & TypeName(x), x.Name
    Next x

FILE_GetSheetsFromFile = True

fin: ActiveWorkbook.Close False
Application.DisplayAlerts = DA
Application.EnableEvents = EE
Application.ScreenUpdating = SU
End Function

Function FILE_GetSheetsFromFile(tmpFilePath) As Boolean
Dim x, sh As Worksheet, DA, EE, SU, i&

DA = Application.DisplayAlerts: Application.DisplayAlerts = False
EE = Application.EnableEvents: Application.EnableEvents = False
SU = Application.ScreenUpdating: Application.ScreenUpdating = False

Workbooks.Open tmpFilePath, False, , , , , True, , , , , , , , True
ReDim tmpFilePath(ActiveWorkbook.Worksheets.Count - 1): i = -1

    With CreateObject("Scripting.Dictionary")
        For Each sh In ActiveWorkbook.Worksheets
            x = .Item(sh)
        Next sh
        tmpFilePath = .Keys
    End With
    
    For Each x In tmpFilePath
        Debug.Print TypeName(x)
        Debug.Print x.Name
    Next x

FILE_GetSheetsFromFile = True

fin: ActiveWorkbook.Close False
Application.DisplayAlerts = DA
Application.EnableEvents = EE
Application.ScreenUpdating = SU
End Function

temp = txt: If Not FILE_GetSheetsFromFile(temp) Then GoTo fin
    For Each x In temp
        Debug.Print TypeName(x)
    Next x

Function FILE_GetSheetsFromFile(tmpFilePath) As Boolean
Dim x, sh As Worksheet, DA, EE, SU, i&

DA = Application.DisplayAlerts: Application.DisplayAlerts = False
EE = Application.EnableEvents: Application.EnableEvents = False
SU = Application.ScreenUpdating: Application.ScreenUpdating = False

Workbooks.Open tmpFilePath, False, , , , , True, , , , , , , , True: ReDim tmpFilePath(ActiveWorkbook.Worksheets.Count - 1): i = -1
    For Each sh In ActiveWorkbook.Worksheets
        i = i + 1: tmpFilePath(i) = sh
        Debug.Print "func " & TypeName(tmpFilePath(i))
    Next sh
FILE_GetSheetsFromFile = True

fin: ActiveWorkbook.Close False
Application.DisplayAlerts = DA
Application.EnableEvents = EE
Application.ScreenUpdating = SU
End Function

Function FILE_GetArraysFromFile(ByVal txtFilePath$, arr1xRanges) As Boolean
Dim x, DA, EE, SU, i&

DA = Application.DisplayAlerts: Application.DisplayAlerts = False
EE = Application.EnableEvents: Application.EnableEvents = False
SU = Application.ScreenUpdating: Application.ScreenUpdating = False

Workbooks.Open txtFilePath, False, , , , , True, , , , , , , , True: i = -1
    For Each x In arr1xRanges
        i = i + 1: arr1xRanges(i) = Evaluate(x).Value2
    Next x
FILE_GetArraysFromFile = True

fin: ActiveWorkbook.Close False
Application.DisplayAlerts = DA
Application.EnableEvents = EE
Application.ScreenUpdating = SU
End Function