Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 27th, 2006, 07:01 PM
Authorized User
 
Join Date: Sep 2006
Location: , NH, USA.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Add and Refresh Excel QueryTable from VBA

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
 
Old September 28th, 2006, 03:03 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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..

 
Old September 28th, 2006, 07:23 AM
Authorized User
 
Join Date: Sep 2006
Location: , NH, USA.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!




Similar Threads
Thread Thread Starter Forum Replies Last Post
[VB2005]Import textfiles to Excel using QueryTable bellaelysium Visual Studio 2005 0 July 28th, 2008 01:32 AM
Refresh Excel QueryTable using VBA buefordTJ Excel VBA 0 October 5th, 2006 03:40 PM
Attribute.add and Refresh Problem Ardvisoor ASP.NET 2.0 Professional 3 July 25th, 2006 12:10 PM
VBA - Form Refresh lxu Access 2 March 4th, 2004 11:13 AM
add column in excel with VBA exarkuun Excel VBA 2 November 19th, 2003 09:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.