Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 June 11th, 2003, 05:15 PM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default Send Multiple Queries to ONE Excel Spreadsheet

Hi everyone. My goal with this question is to learn to transfer multiple queries (crosstab queries in Access 2002) to a single excel spreadsheet.

I thought of using the Do.Cmd.TransferSpreadsheet acExport command in the VB code of Access, but I soon realized that I couldn't identify where the data would be put specifically into a particular cell of the spreadsheet.

Hence, my thought process is telling me that if I'm able to sendout the crosstab queries to different tabs within a single spreadsheet, I can then have a master tab in that spreadsheet calling the values from the outputted tabs, thereby simply filling in the blanks that I need for my report in MsExcel.

An example of what my excel spreadsheet (report) would contain/show is as follows:

Total # of Customers-------------------------Loc1-----Loc2-----Loc3
454-------------------------------------------300------105------49
Customers Serviced within the last 30 days
322-------------------------------------------180------102------40
Customers Serviced past 30 days
132-------------------------------------------120------003------9

Where...
-Total # of Customers
-Customers Serviced within the last 30 days
-Customers Serviced past 30 days
... represents an individual query (3 queries total). And the Loc1, Loc2, and Loc3 are the 3 different locations from which we do business (also from the same queries).

Thanks in advance for your support guys.

~Danny
__________________
~Danny
 
Old June 20th, 2003, 11:44 AM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default

does anyone know the vba code to sendout data to a particular cell of excel instead of the entire query to a 'tab' in excel?

~Danny
 
Old June 23rd, 2003, 08:31 AM
Authorized User
 
Join Date: Jun 2003
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This will get you there....

    Set appExcel = New Excel.Application
    Set wbkNew = appExcel.Workbooks.Add
    Set wksNew = wbkNew.Worksheets.Add
    appExcel.Visible = False

    'Make Column Headings
    With wksNew
        .Cells(1, 1).Value = "(Your Data)"
    End With


John





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Spreadsheet Help SteveV VBScript 0 October 10th, 2006 04:42 PM
Excel Spreadsheet Help SteveV BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 0 October 10th, 2006 04:41 PM
Excel Spreadsheet Help SteveV BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 0 October 10th, 2006 04:40 PM
Fill Spreadsheet with 2 separate SQL Queries rweide Classic ASP Databases 0 February 16th, 2005 11:35 AM
multiple instances of same spreadsheet opening Jingels Excel VBA 1 January 31st, 2005 02:03 AM





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