Change Power Query Data Source

0
votes

EDIT:

I debugged and found out that even though I can see all the query tables in front of me in the referenced worksheets, Excel counted and found 0 query tables in any worksheets.

EDIT 2:

So I found that QueryTables exists under ListObjects. So now, I am attempting to create a new data source connection like so:

tw.Worksheets(2).ListObjects(1).QueryTable.Connection = "OLEDB;" & M1
tw.Worksheets(5).ListObjects(1).QueryTable.Connection = "OLEDB;" & M1
tw.Worksheets(3).ListObjects(1).QueryTable.Connection = "OLEDB;" & M2
tw.Worksheets(6).ListObjects(1).QueryTable.Connection = "OLEDB;" & M2
tw.Worksheets(4).ListObjects(1).QueryTable.Connection = "OLEDB;" & M3
tw.Worksheets(7).ListObjects(1).QueryTable.Connection = "OLEDB;" & M3

ORIGINAL POST

I am looking to use VBA to further automate a reporting process. The first step is to refresh all of the data based on the quarter and year data entered on the summary page.

Here is what it currently looks like Qd_sht

My goal is to do 3 things and a be able to update the data.

  1. Change Quarter
  2. Change Year reporting on
  3. Click Refresh All button

Below is my code that is I am attempting to use to do this. I do have Excel 16, and I am trying to update the Macro Workbook with excel workbook connections. Where I am running into the issue with "Subscript out of range":
tw.Worksheets(2).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M1

Public Sub Refresh_All()

    Dim tw As ThisWorkbook: Set tw = ThisWorkbook
    Dim qd As Worksheet: Set qd = tw.Worksheets("Quarterly Data")
    Dim yr As String: yr = qd.Range("H11").Value2
    Dim qrtr As String: qrtr = qd.Range("G11").Value2
    Dim fpath As String: fpath = "X:\Dump Report for Loans\" & yr
    Dim ldr As String: ldr = "\Loan Dump Report (000.Original).xlsx"
    Dim M1 As String
    Dim M2 As String
    Dim M3 As String

    If qrtr = "Q1" Then
        M1 = fpath & "\02-01-" & yr & ldr
        M2 = fpath & "\03-01-" & yr & ldr
        M3 = fpath & "\04-01-" & yr & ldr

        tw.Worksheets(2).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M1
        tw.Worksheets(2).QueryTables(1).Refresh
        tw.Worksheets(3).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M2
        tw.Worksheets(3).QueryTables(1).Refresh
        tw.Worksheets(4).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M3
        tw.Worksheets(4).QueryTables(1).Refresh
    ElseIf qrtr = "Q2" Then
        M1 = fpath & "\05-01-" & yr & ldr
        M2 = fpath & "\06-01-" & yr & ldr
        M3 = fpath & "\07-01-" & yr & ldr

        tw.Worksheets(2).QueryTables(1).Item Connection:=M1
        tw.Worksheets(2).QueryTables(1).Refresh
        tw.Worksheets(3).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M2
        tw.Worksheets(3).QueryTables(1).Refresh
        tw.Worksheets(4).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M3
        tw.Worksheets(4).QueryTables(1).Refresh
    ElseIf qrtr = "Q3" Then
        M1 = fpath & "\08-01-" & yr & ldr
        M2 = fpath & "\09-01-" & yr & ldr
        M3 = fpath & "\010-01-" & yr & ldr

        tw.Worksheets(2).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M1
        tw.Worksheets(2).QueryTables(1).Refresh
        tw.Worksheets(3).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M2
        tw.Worksheets(3).QueryTables(1).Refresh
        tw.Worksheets(4).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M3
        tw.Worksheets(4).QueryTables(1).Refresh
    ElseIf qrtr = "Q4" Then
        M1 = fpath & "\11-01-" & yr & ldr
        M2 = fpath & "\12-01-" & yr & ldr
        M3 = fpath & "\01-01-" & yr + 1 & ldr

        tw.Worksheets(2).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M1
        tw.Worksheets(2).QueryTables(1).Refresh
        tw.Worksheets(3).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M2
        tw.Worksheets(3).QueryTables(1).Refresh
        tw.Worksheets(4).QueryTables(1).Item Connection:="EXCEL.Worksheets;" & M3
        tw.Worksheets(4).QueryTables(1).Refresh
    Else:
        MsgBox "ERROR!! NO QUARTER ENTERED!!"
    End If

    ThisWorkbook.RefreshAll

End Sub

Thanks in advance for your help!

1
excelvbapowerqueryexcel-2016
The error suggests the index (that you're providing in brackets) exceeds the collection count (so you're referring to the n+1th item of some collection that only contains n objects). For example, you refer to tw.Worksheets(2), but if tw only has 1 worksheet, I think you will get an error like the one you're getting. Another example would be if tw.Worksheets(2) doesn't contain any query tables, then tw.Worksheets(2).QueryTables(1) will give you an error. – chillin
For the purposes of debugging, I would recommend sticking a Debug.Assert tw.Worksheets(2).QueryTables.Count > 0 or Debug.Print tw.Worksheets(2).QueryTables.Count before your code. Or Dim someVariable as QueryTables, then Set someVariable = tw.Worksheets(2).QueryTables, then inspect someVariable in the Locals Window of the VB Editor -- to see if its state is what you expect. – chillin
@chillin I am not at work now but will attempt this on Monday. Thank you! – A Cohen
Are you using File.Contents function in your M/Power Query code? (If you created your query via the GUI, it may have auto-generated your code for you, but you can view it via Query Editor -> Advanced Editor.) My point is that if you make the quarter and year cells into named ranges, you can then pick up their values in Power Query itself; which then lets you refresh the query using a dynamically/conditionally determined file path. – chillin
No problem, glad you got it all sorted out. – chillin

1 Answers

0
votes

After some outside discussion with @chillin, we came up with a solution that would use M/Power Query to make it more dynamic. Below is the code that works beautifully. Thanks again @chillin.

let  

    GetNamedRangeInCurrentWorkbook = (nameOfRange as text) as any => Excel.CurrentWorkbook(){[Name=nameOfRange]}[Content][Column1]{0},  
    quarterFromSheet = Text.From(GetNamedRangeInCurrentWorkbook("quarter")),  
    yearFromSheet = Text.From(GetNamedRangeInCurrentWorkbook("year")),  
    quarterNumber = try Number.From(Text.End(quarterFromSheet, 1)) otherwise error "Could not convert 'quarter' to a number",   
    filename = "Loan Dump Report (000.Original).xlsx",  
    startOfFinancialYear = #date(Number.From(yearFromSheet), 2, 1),  
    MonthInFilePath = (startDate as date, quarter as number, month as number) as date => Date.AddMonths(startDate, ((quarter-1) * 3) + month - 1), 
    DetermineFilePath = (month as number) as text =>  

    let
        targetMonth = MonthInFilePath(startOfFinancialYear, quarterNumber, month), folderPath = "X:\Dump Report for Loans\" & Date.ToText(targetMonth, "yyyy") & "\", subFolder = Date.ToText(targetMonth, "MM-dd-yyyy"), fullFilePath = folderPath & subFolder & "\" & filename
    in
        fullFilePath  

in
    DetermineFilePath