Option Explicit

    '-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
    '~Input file path here in between the quotation marks
    Const FILE_PATH As String = "C:\file.xlsx"

    '~Input name of Sheet with Pivot Tables here in between the quotation marks
    Const PIVOT_SHEET_NAME As String = "IQ_Pivot"
    '-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Sub Excelthingtest()
    Dim arrayOfData As Variant
    arrayOfData = GetData()
End Sub

Private Function GetData() As Variant
    Dim excelApplication As Object
    Set excelApplication = CreateObject("Excel.Application")
    Dim targetBook As Object
    Set targetBook = excelApp.Workbooks.Open(FILE_PATH)
    Dim targetSheet As Object
    Set targetSheet = targetBook.Sheets(PIVOT_SHEET_NAME)
    Dim targetLastRow As Long
    Dim targetLastColumn As Long
    targetLastRow = targetSheet.Cells(Rows.Count, 1).End(-4162).Row
    targetLastColumn = targetSheet.Cells(1, Columns.Count).End(-4159).Column
    GetData = targetSheet.Range(Cells(1, 1), Cells(targetLastRow, targetLastColumn))
    excelApplication.DisplayAlerts = False
    excelApplication.Quit
End Function