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 |
можно как-то это обойти?
