Wrox Programmer Forums
BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3
This is the forum to discuss the Wrox book Access 2007 VBA Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein; ISBN: 9780470047033
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 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 October 9th, 2007, 04:23 PM
Registered User
Join Date: Oct 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Chapter 16: Exporting query data to Excel

This is an excellent chapter!

I'm trying to develop a form that has a list box that shows 5 or 6 queries from which Users can select, to export data to Excel. I don't want to list ALL the queries in the database (but this chapter also has an excellent way to do that).

Problem: On page 544, it says "If you don't want Users to be able to choose every Query in the database, the [u]names</u> of the object can always be hard-coded into the Row Source property for the list box." I have tried modifying various code in VBA to include a single specific query, or in the Form's property sheet -- nothing works. Can anyone help?

There are 2 examples of VBA code in the book -- both are used in the Form's OnLoad Event.

'Example 1 -- setting the list data for a List Box control on form load

Private Sub Form_Load()

    Dim qdQueryName As QueryDef

    'Clear the list if it is already filled
    lstExport.RowSource = "" '(lstExport is the name of the List Box)
    lstExport.RowSourceType = "Value List"

    'Add all of the Query names to the List Box
    For Each qdQueryName In Application.CurrentDb.QueryDefs
        If (InStr(1, qdQueryName.Name, "~") = 0) Then
            Me.lstExport.AddItem qdQueryName.Name
        End If

End Sub

'Example 2 -- setting the list data for a List Box control on form load from a SQL statement

Private Sub Form_LoadOther()

    'Create the SQL Statement
    Dim strSQL As String
    strSQL = _
        "SELECT MSysObjects.Name " & _
        "FROM MSysObjects " & _
        "WHERE (((MSysObjects.Name) Not Like ""~*"") AND ((MSysObjects.Type)=5));"

    'Set the Row Sourse with the SQL Statement
    'lstExport is the name of the List Box.
    Me.lstExport.RowSourceType = "Table/Query"
    Me.lstExport.RowSource = strSQL

End Sub

How can I specifically list several query names in the RowSource property to fill the list box? Thanks for any suggestions.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting data to Excel lrickett Beginning VB 6 3 February 10th, 2011 01:27 AM
help exporting data to excel MarkGT Classic ASP Basics 5 April 29th, 2008 06:13 PM
Exporting Query Data From Access to Excel JimInSouthernCal Access VBA 3 December 21st, 2007 08:34 AM
Query data and exporting data from one sheet to an testman Excel VBA 2 April 28th, 2005 02:40 PM
Exporting Query Results to Excel File redrobot5050 Access VBA 2 October 7th, 2004 05:26 PM

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