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 TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
NorieAnalyst Assistant Commented:
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
APD TorontoSoftware DeveloperAuthor Commented:
instead of a fixed path, can i  export to the user's desktop?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 TorontoSoftware DeveloperAuthor 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 TorontoSoftware DeveloperAuthor 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
NorieAnalyst Assistant Commented:
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 TorontoSoftware DeveloperAuthor 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 TorontoSoftware DeveloperAuthor Commented:
how do i do early binding...

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.