Parameter Query in VBA

[j_Southern]

I would welcome some help with the following code

Sub NewSQL()

Const SAGECONN1 As String = "ODBC;DSN=SAGE LINE 100;UID=test;;"
Dim qtSage As QueryTable
Dim rngDest As Range
Dim strSQL As String

With Sheets("Sheet1")
.Range("A:E").Clear
FROM = .Range("H2").Value
TILL = .Range("H4").Value
End With
       strSQL = MakeSQL1001(FROM, TILL)
       Set rngDest = Sheets("Sheet1").Range("A1")
        Set qtSage = Worksheets("Sheet1").QueryTables.Add(SAGECONN1, rngDest, strSQL)
          qtSage.RefreshStyle = xlOverwriteCells
          Sheets("Sheet1").Range("A1").Select
            Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False *
   
End Sub
Function MakeSQL1001(FROM, TILL)
strSQL = "SELECT "

strSQL = strSQL & "SALES_TRANSACTIONS.TRANSACTION_DATE, SALES_TRANSACTIONS.TRANSACTION_TYPE, "

strSQL = strSQL & "SALES_TRANSACTIONS.VAT_AMOUNT, SALES_TRANSACTIONS.SALES_CONTROL_VALUE "
strSQL = strSQL & "FROM "
strSQL = strSQL & "ACCOUNTING_SYSTEM.SALES_TRANSACTIONS SALES_TRANSACTIONS"

strSQL = strSQL & "WHERE "

strSQL = strSQL & "(SALES-TRANSACTIONS.TRANSACTION_DATE >='" & Format(FROM, "yyyy-mm-dd") & "' "
strSQL = strSQL & "AND "

strSQL = strSQL & "SALES_TRANSACTIONS.TRANSACTION_DATE >='" & Format(TILL, "yyyy-mm-dd") & "')"
 MakeSQL1001 = strSQL
End Function

The user enters the dates "FROM" and "TILL" in cells H2 & H4 and runs the query. If I do clear the old manual query from A1 then I get an error message for the Refresh line.(marked *)
"Object Variable or With block variable not set."
If I leave cell a1 uncleared, then running the code simply refreshes the old manual query.
If the manual query is removed completely then I get the same error message.
I have remed out each SQL line in turn without success
A version of this code worked fine in Excell 2007 but I am now using 2010
John

[OnErrorGoto0]

Probably something like

Set qtSage = rngDest.ListObject.QueryTable
with qtSage
   .commandtext = strSQL
   .Refresh BackgroundQuery:=False
End With

rather than adding or clearing each time.

[j_Southern]

Thanks I see what you mean. Will put it to the test now
John

[Lifeseeker]

Hi,

I'm studying the code as I may potentially develop a code like this to extract data from a SQL server.

My question at this point is: where are the results displayed in Excel sheet?

Thanks

[OnErrorGoto0]

See forum rule 2 - please start your own thread.