Export Query Results to Excel

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.
APD TorontoAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
here is a sample of early binding

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Visible = True
0
 
Rey Obrero (Capricorn1)Commented:
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
 
NorieVBA ExpertCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
APD TorontoAuthor Commented:
instead of a fixed path, can i  export to the user's desktop?
0
 
Rey Obrero (Capricorn1)Commented:
yes

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

docmd.transferspreadsheet acexport,8, "queryName", strPath & "myExcel.xls", true
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
APD TorontoAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
dim colCnt as integer
colCnt =Sheet.usedRange.columns.count
0
 
Rey Obrero (Capricorn1)Commented:
dim colCnt as integer, varChar as string
colCnt =Sheet.usedRange.columns.count

varChar=chr(64 + colCnt),  if colcnt is 7, varChar is  "G"
0
 
APD TorontoAuthor Commented:
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
 
NorieVBA ExpertCommented:
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
 
Rey Obrero (Capricorn1)Commented:
APD_Toronto,
upload sample db with the table and query
0
 
APD TorontoAuthor Commented:
capricorn, i'm following your code example, but is it possible to enable the inteliSense dropdown after the period for the excel objects
0
 
Rey Obrero (Capricorn1)Commented:
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
 
APD TorontoAuthor Commented:
how do i do early binding...

I think i'm almost done
0
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.

All Courses

From novice to tech pro — start learning today.