|
Subject:
|
Add and Refresh Excel QueryTable from VBA
|
|
Posted By:
|
DaveJoyG
|
Post Date:
|
9/27/2006 7:01:01 PM
|
I'm having trouble creating a query table from VBA
I am pulling data from an SQL database (ODBC). The field titles come in but no data. I receive no errors and the data load is well within the limits of a single sheet for lines
If I "edit" the query in MS Query, make no changes and exit Query, the data shows up fine
With ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=ActiveSheet.Range("A3"), _
Sql:=sSql)
.RefreshStyle = xlOverwriteCells
.Refresh
End With
For organization I setup a long string to handle the SELECT statement
sSql = "SELECT inv_trx.trx_date, inv_trx.trx_qty,"
sSql = sSql & " inv_trx.unit_price, inv_trx.unit_cost,"
sSql = sSql & " inv_trx.ord_qty, inv_trx.comm_cost,"
sSql = sSql & " inv_trx.addt_cost, inv_trx.cust_no,"
sSql = sSql & " inv_trx.order_no, inv_trx.lin_no,"
sSql = sSql & " inv_trx.doc_no, inv_trx.trx_type,"
sSql = sSql & " ord_hedr.ord_class, ord_hedr.ord_type,"
sSql = sSql & " item.prod_cat, customer.reg_code, customer.exclude_sa"
sSql = sSql & " FROM customer, inv_trx, item, ord_hedr"
sSql = sSql & " WHERE customer.cust_no = inv_trx.cust_no "
sSql = sSql & " AND customer.cust_no = ord_hedr.cust_no "
sSql = sSql & " AND inv_trx.order_no = ord_hedr.order_no "
sSql = sSql & " AND inv_trx.item_no = item.item_no "
sSql = sSql & " AND inv_trx.manu_no = item.manu_no "
sSql = sSql & " AND ((customer.exclude_sa=0) "
sSql = sSql & " AND (inv_trx.trx_date>="
sSql = sSql & STARTDATE
sSql = sSql & " AND inv_trx.trx_date<"
sSql = sSql & ENDDATE
sSql = sSql & ") "
sSql = sSql & " AND (ord_hedr.ord_class<>'M'"
sSql = sSql & " And ord_hedr.ord_class<>'P') "
sSql = sSql & " AND (ord_hedr.ord_type<>'S')) "
sSql = sSql & " ORDER BY inv_trx.trx_date"
From what I found on the web nobody else is having this issue so I must be missing something simple
Help!
Dave
|
|
Reply By:
|
jrogers
|
Reply Date:
|
9/28/2006 3:03:07 AM
|
are you sure your date format is correct in STARTDATE and ENDDATE? try removing these conditions. If you recieve data, then I would guess this was the problem..
|
|
Reply By:
|
DaveJoyG
|
Reply Date:
|
9/28/2006 7:23:12 AM
|
Thanks! that was it. Although I formated the variable as a Date value I forgot that it treats it as a number and that is what is passed to the sql string. I guess I'll have to build the date/time value independantly (simple to do) and pass it as a text string.
A second pair of eyes always catches the simple stuff so easy
Thanks again!
|
|
