none
Convert Existing Query to VBA Code

    Question

  • I already have a query built using and ODBC driver connection to our Legacy Financial System.

    I was wondering if there is a way to easily convert this query into VBA Code? Something along the lines of converting a query to SQL.

    The reason I want to do this, is because I'd like to add additional code that will automatically populate the criteria fields based on the time the file is opened and executed.  Currently anytime the file is opened, I have to manually adjust the query.  This is a file that I want to schedule to run on a monthly basis to create a monthly close support file.

    Any advice/suggestions would be appreciated.

    Thanks in advance.

    JMData Consultant

    Wednesday, August 28, 2013 1:14 PM

All replies

  • Record a Macro while creating a connection and building your query in query builder is the easiest way to get the code you need to change (i.e. it sounds like you're looking to change dates or something similar).

    here is some sample code which has a separate sub with your sql.

    sub pullData
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                        "ODBC;DSN=t01;UID=" & userID() & ";;", Destination:=Range("$A$1")).QueryTable
                        .CommandText = sqlQ
                        .RowNumbers = False
                        .FillAdjacentFormulas = False
                        .PreserveFormatting = True
                        .RefreshOnFileOpen = False
                        .BackgroundQuery = True
                        .RefreshStyle = xlInsertDeleteCells
                        .SavePassword = False
                        .SaveData = True
                        .AdjustColumnWidth = True
                        .RefreshPeriod = 0
                        .PreserveColumnInfo = True
                        .ListObject.DisplayName = tName
                        .Refresh BackgroundQuery:=False
                                        
                        
                        
                    End With
    
    end sub
    
    function sqlQ as string
    
                qB = "SELECT mh.METER_NUMBER, mh.ACCOUNT_NUMBER, mh.UNITS, mh.AMOUNT, dmh.ENERGY_KW, dmh.R_KVAR_READING, dmh.FIRST_YEAR_MONTH_HIST, dmh.DIFF_READING_DATE, mh.DAYS, mh.FCA" & Chr(13) & "" & Chr(10) & "FROM I.DEMAND_METER_HISTORY "
                qB = qB & "dmh, I.METER_HISTORY mh" & Chr(13) & "" & Chr(10) & "WHERE (mh.ACCOUNT_NUMBER=dmh.account_number And mh.ACCOUNT_NUMBER = '" & actNum & "') AND (mh.METER_NUMBER=dmh.meter_number) AND (mh.SEQUENCE_VALUE=dmh.sequence_value)"
    
    sqlQ = QB
    
    end function

    This code isn't a perfect example, I use a separate function for my sql because based on some user inputs the query could change. userID() is a function I have to auto populate the users login ID from windows.

    Hopefully this gets you headed in the correct direction.

    Wednesday, August 28, 2013 2:49 PM
  • Zachariah,

    Thanks for the information. Just wondering what the .RefreshPeriod=0 in your code is doing?  Can you elaborate?

    I'll try your first suggestion of recording the Macro and creating the database connection, as my information doesn't require any USER ID or other network/system credentials.

    JMData Consultant

    Wednesday, August 28, 2013 5:12 PM
  • Took Zachariah's first suggestion and did the record macro to get the code of the query creation.

    I'm having trouble placing the date in the criteria fields.  Before my attempts at changing the information the dates read '2013-08-01' and '2013-08-31'.

    The Code I have runs fine up to the point of the Data Connection.  The only thing I changed was the date criteria fields.

    Receiving Run-time error '1004' Application-defined or object-defined error

    I even put back the original code and still got the Run-time error code.

    The VBA Code I have is as follows:

    Application.Speech.Speak "Variables Being Created."
    Dim TODAY As Date
    Dim ENDOFMONTH As Date
    Dim BEGINOFMONTH As Date
    Dim STRENDOFMONTH As String
    Dim STRBEGINOFMONTH As String

    TODAY = Now()
    ENDOFMONTH = WorksheetFunction.EoMonth(TODAY, 0)
    BEGINOFMONTH = WorksheetFunction.EoMonth(TODAY, -1) + 1
    Application.Speech.Speak "Dates Have Been Set. Now attempting date conversion for criteria fields"
    Application.Speech.Speak "Attempting to Display Results DATE CONVERSION FIELDS"
    STRENDOFMONTH = (Format(ENDOFMONTH, "YYYY") & "-" & Format(ENDOFMONTH, "MM") & "-" & Format(ENDOFMONTH, "DD"))
    STRBEGINOFMONTH = (Format(BEGINOFMONTH, "YYYY") & "-" & Format(BEGINOFMONTH, "MM") & "-" & Format(BEGINOFMONTH, "DD"))
    Application.Speech.Speak "Displaying Beginning of Month Date"
    MsgBox (STRBEGINOFMONTH)
    Application.Speech.Speak "Displaying End of Month Date"
    MsgBox (STRENDOFMONTH)

     {{{{{{ THE RESULTS OF MY DATE FORMATTING CODE SHOW AS 2013-08-01 AND 2013-08-31 }}}}}


    Application.Speech.Speak "ATTEMPTING DATA CONNECTION"
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=vpc.udd;" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandText = Array( _
            "SELECT APPAYMENT.COMPANY, APPAYMENT.CHECKDATE, APPAYMENT.CHECK, APPAYMENT.TIME, APPAYMENT.VENDOR, APPAYMENT.INVOICE, APPAYMENT.PAYMENT, APPAYMENT.DISCOUNT, APPAYMENT.PAYEE, APPAYMENT.SOURCE, APPAYMENT" _
            , _
            ".SYSDATE, APPAYMENT.CKREGCOMP" & Chr(13) & "" & Chr(10) & "FROM root.APPAYMENT APPAYMENT" & Chr(13) & "" & Chr(10) & "WHERE (APPAYMENT.COMPANY='01') AND (APPAYMENT.CHECKDATE Between {d 'strbeginofmonth')} And {d 'strendofmonth')})" _
            )
            Application.Speech.Speak "CRITERIA HAS BEEN SET"
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_Query_from_vpc4"
            .Refresh BackgroundQuery:=False
        End With
        Columns("M:M").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Delete Shift:=xlToLeft
        Range("M1").Select
        ActiveCell.FormulaR1C1 = "monthend"
        Range("M2").Select
        ActiveCell.FormulaR1C1 = "=EOMONTH(R[-7]C[2],0)"
        Range("N1").Select
        ActiveCell.FormulaR1C1 = "CHECK#"
        Range("N2").Select
        ActiveCell.FormulaR1C1 = "=TRIM(RC[-11])"
        Range("M2").Select
        ActiveCell.FormulaR1C1 = "=EOMONTH(RC[2],0)"
        Range("O1").Select
        ActiveCell.FormulaR1C1 = "CHECKDATE2"
        Range("O2").Select
        ActiveCell.FormulaR1C1 = "=RC[-13]"
        Range("O2").Select
        Selection.NumberFormat = "m/d/yyyy"
        Selection.AutoFill Destination:=Range("Table_Query_from_vpc4[CHECKDATE2]")
        Range("Table_Query_from_vpc4[CHECKDATE2]").Select
        Range("M2").Select
        Selection.NumberFormat = "m/d/yyyy"
        Selection.AutoFill Destination:=Range("Table_Query_from_vpc4[monthend]")
        Range("Table_Query_from_vpc4[monthend]").Select
        Columns("N:N").EntireColumn.AutoFit
        Columns("O:O").EntireColumn.AutoFit
        Columns("M:M").EntireColumn.AutoFit
        Range("P1").Select
        ActiveCell.FormulaR1C1 = "CHECKAMOUNT"
        Range("P2").Select
        ActiveCell.FormulaR1C1 = "=RC[-9]/100"
        Columns("P:P").Select
        Selection.Style = "Comma"
        Columns("P:P").EntireColumn.AutoFit
        Range("Q1").Select
        ActiveCell.FormulaR1C1 = "DISCOUNTAMOUNT"
        Range("Q2").Select
        ActiveCell.FormulaR1C1 = "=RC[-9]/100"
        Range("Q2").Select
        Selection.Style = "Comma"
        Selection.AutoFill Destination:=Range("Table_Query_from_vpc4[DISCOUNTAMOUNT]" _
            )
        Range("Table_Query_from_vpc4[DISCOUNTAMOUNT]").Select
        Columns("Q:V").Select
        Selection.Style = "Comma"
        ActiveWindow.LargeScroll ToRight:=-1
        Range("R1").Select
        ActiveCell.FormulaR1C1 = "TOTAL AP CLEARED"
        Range("R2").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
        Range("S1").Select
        ActiveCell.FormulaR1C1 = "CHECK VENDOR"
        Range("S2").Select
        ActiveCell.FormulaR1C1 = "=R[-7]C[-14]"
        Range("T1").Select
        ActiveCell.FormulaR1C1 = "PAYEE2"
        Range("T2").Select
        ActiveCell.FormulaR1C1 = "=RC[-11]"
        Range("S2").Select
        ActiveCell.FormulaR1C1 = "=RC[-14]"
        Range("S2").Select
    End Sub



    Wednesday, August 28, 2013 6:59 PM
  • Some additional information that may prove critical:

    The ODBC Driver was created through a third-party program outside of the normal Windows Procedure as I understand it.

    Is there possibly a Reference that has to be enabled in order for VBA code to establish this connection?

    Wednesday, August 28, 2013 7:43 PM
  • Further Testing Shows that the code captured through the Record Macro Process works by itself.

    When I try to combine that with my code attempting to change the criteria, the code bombs out.

    Is there something special that has to be done to pass variables into another part of the code?  I'm putting this all in one combine SUB routine.

    Thoughts / suggestions??

    JMData Consultant

    ********* UPDATE 8/29/2013 @ 10:05AM NY TIME *************

    Following Code works Only when the Sheet Is Completely Blank.  Can someone explain why the sheet has to be blank for the code to run? Otherwise I get 1004 Error Code.

    Sub alltogether()

    Application.Speech.Speak "Variables Being Created."
    Dim TODAY As Date
    Dim ENDOFMONTH As Date
    Dim BEGINOFMONTH As Date
    Dim STRENDOFMONTH As String
    Dim STRBEGINOFMONTH As String

    TODAY = Now()
    ENDOFMONTH = WorksheetFunction.EoMonth(TODAY, -1)
    BEGINOFMONTH = WorksheetFunction.EoMonth(TODAY, -2) + 1
    Application.Speech.Speak "Dates Have Been Set. Now attempting date conversion for criteria fields"
    'Application.Speech.Speak "Attempting to Display Results DATE CONVERSION FIELDS"
    STRENDOFMONTH = (Format(ENDOFMONTH, "YYYY") & "-" & Format(ENDOFMONTH, "MM") & "-" & Format(ENDOFMONTH, "DD"))
    STRBEGINOFMONTH = (Format(BEGINOFMONTH, "YYYY") & "-" & Format(BEGINOFMONTH, "MM") & "-" & Format(BEGINOFMONTH, "DD"))
    'Application.Speech.Speak "Displaying Beginning of Month Date"
    'MsgBox (STRBEGINOFMONTH)
    'Application.Speech.Speak "Displaying End of Month Date"
    'MsgBox (STRENDOFMONTH)


    '********* RUNS QUERY ***************

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=vpc.udd;" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandText = Array( _
            "SELECT ARPAYMENT.AMOUNT, ARPAYMENT.BADCHECK, ARPAYMENT.CHECK, ARPAYMENT.CHECKDATE, ARPAYMENT.COMPANY, ARPAYMENT.CUSTOMER, ARPAYMENT.INVOICE, ARPAYMENT.SYSDATE, ARPAYMENT.TRANSCODE" & Chr(13) & "" & Chr(10) & "FROM root.ARPAYMENT" _
            , _
            " ARPAYMENT" & Chr(13) & "" & Chr(10) & "WHERE (ARPAYMENT.CHECKDATE Between {d '" & STRBEGINOFMONTH & "'} And {d '" & STRENDOFMONTH & "'})" _
            )
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_Query_from_vpc"
            .Refresh BackgroundQuery:=False
        End With
    Application.Speech.Speak "bebebebebe that's all folks."
    End Sub

    Thursday, August 29, 2013 1:58 PM
  • You want to convert SQL to VBA?  Check out this link.

    http://allenbrowne.com/ser-71.html

    That method works great!  I've used it to convert some seriously complex SQL to VBA!  It worked great!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, August 30, 2013 3:08 AM