Changing Microsoft Query in Excel with Python (pywin32) or VBA
Question: I need to create reports for the financial year of the individual sales of
each customer (around 500) from 1-Apr 2014 to 31-Mar 2015. Last year when I
did this I went in to each report from the previous year and simply changed
the date in the query so it brought through that financial year. However, our
customer base has grown even further and now it looks like I'll spend hours
doing this unless I find out a way to change the query in the background.

I can go through each worksheet in the folder containing these reports and
open each file for editing:

    
    
    import os, pythoncom
    from win32com.client import Dispatch    
    
    for path, subdirs, files in os.walk("location\of\folder"):
        # Open each workbook
        for filename in files:
            xl = Dispatch("Excel.Application")
            wb = xl.Workbooks.Add(path+"\\"+filename)
            ws = wb.Worksheets(1)
    
            # Method to alter the query here!!!!
            # Would even be open to doing this with VBA and just calling a macro to run
            #    change the query if that's possible?!
    
            wb.Close()
            xl.Quit()
    
    pythoncom.CoUninitialize()
    

Any help would be greatly appreciated, as it's likely to save me hours upon
hours of monotonous work.

Many thanks!

I have just tried the following method for trying to change the SQL using VBA.

    
    
    Sub change_date()
    
    Sheets(1).Select
    
    ActiveSheet.QueryTables(1).CommandText = Replace(ActiveSheet.QueryTables(1).CommandText, "WHERE (Customers.InvoiceDate>={ts '2013-04-01 00:00:00'} And Customers.InvoiceDate<={ts '2014-03-31 00:00:00'})", "WHERE (Customers.InvoiceDate>={ts '2014-04-01 00:00:00'} And Customers.InvoiceDate<={ts '2015-03-31 00:00:00'})")
    
    
    
    End Sub
    

But I am getting a Runtime (9) error: Subscript Out of Range.

Any help would be appreciated.


Answer: I've figured it out for all who are concerned.

    
    
    Sub change_date()
    
    Dim sh As Worksheet, LO As ListObject, QT As QueryTable
    
    Set sh = ActiveSheet
    Set QT = sh.ListObjects.Item(1).QueryTable
    
    With QT
        .CommandType = xlCmdSql
        .CommandText = Replace(QT.CommandText, "WHERE (Customers.InvoiceDate>={ts '2013-04-01 00:00:00'} And Customers.InvoiceDate<={ts '2014-03-31 00:00:00'})", "WHERE (Customers.InvoiceDate>={ts '2014-04-01 00:00:00'} And Customers.InvoiceDate<={ts '2015-03-31 00:00:00'})")
        .Refresh
    End With
    
    End Sub
    

