Improve company productivity with a Business Account.Sign Up

x
?
Solved

Export Query Results to Excel

Posted on 2012-03-27
15
Medium Priority
?
385 Views
Last Modified: 2012-03-27
Hi Experts,

I have a query of X rows by Y xolumns, both X and Y I determine at run-time, and I get the propper results.

Considering that X and Y are unkown at design time, my question is how can I export the Query's results into Excel, and still have a Totals row at the end?

Thanks.
0
Comment
Question by:APD Toronto
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37773488
you can export the query to excel with

docmd.transferspreadsheet acexport,8, "queryName","C:\folder\myExcel.xls",true

after exporting, you need to open the excel file using VBA codes to set the formula to the corresponding cells or use the codes from this thread

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27209975.html#a36215605

rowCnt=Sheet.usedRange.rows.count  will give you the total rows used

you can use the count to set the formula to the correct cells
0
 
LVL 37

Expert Comment

by:Norie
ID: 37773497
When exporting a query to Excel from Access you don't need to specify the no of columns(fields) and rows(records).

How and when are the totals added?
0
 

Author Comment

by:APD Toronto
ID: 37773521
instead of a fixed path, can i  export to the user's desktop?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37773543
yes

dim strPath
strPath=environ("userProfile") & "\Desktop\

docmd.transferspreadsheet acexport,8, "queryName", strPath & "myExcel.xls", true
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37773747
<and still have a Totals row at the end?>
Do you mean the totals using the "Totals" button in Datasheet view of the query?
This will include these totals:
DoCmd.OutputTo acOutputQuery, "YourQuery", acFormatXLS, "C:\YourFolder\YourFile.xls"

Still not quite sure what you mean by:
"I have a query of X rows by Y xolumns, both X and Y I determine at run-time"
a query's Columns are fixed, and the number of rows depends on any criteria...
So it is not clear what you mean by:<I have a query of X rows by Y xolumns, both X and Y I determine at run-time, >

Can you clarify...?

JeffCoachman
0
 

Author Comment

by:APD Toronto
ID: 37773909
Jeff, see http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27648929.html

capricorn, using the rowcnt + 1 would give me the next row, say 29, but how would i get the col count and use that to:

A29 = "T O T A L S :"
B29 = Sum(B1:B28)
C29 = Sum(C1:C28)
...
...
?29 = Sum(?1:?28)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37773918
dim colCnt as integer
colCnt =Sheet.usedRange.columns.count
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37773936
dim colCnt as integer, varChar as string
colCnt =Sheet.usedRange.columns.count

varChar=chr(64 + colCnt),  if colcnt is 7, varChar is  "G"
0
 

Author Comment

by:APD Toronto
ID: 37773942
If colcount is 3, how do I know the max is D29?  

Do I need a function to convert number to letter, or can i access a cell with 2 ints?
0
 
LVL 37

Expert Comment

by:Norie
ID: 37773979
You can try this for the totals.

It assumes column A wil be populated for each row of data.

Dim TotalsRow As Long

Dim NoCols As Long

TotalsRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

NoCols = Cells(1, Columns.Count).End(xlToLeft).Column - 1

Range("A" & TotalsRow) = "T O T A L S"

Range("B" & TotalsRow).Resize(, NoCols) = "=SUM(R2C:R[-1]C)"

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37773997
APD_Toronto,
upload sample db with the table and query
0
 

Author Comment

by:APD Toronto
ID: 37774038
capricorn, i'm following your code example, but is it possible to enable the inteliSense dropdown after the period for the excel objects
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37774047
you have to add  the excel object library to your references and use early binding instead of late binding (the codes from the link is late binding)
0
 

Author Comment

by:APD Toronto
ID: 37774063
how do i do early binding...

I think i'm almost done
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37774064
here is a sample of early binding

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Visible = True
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question