Show query results in Excel spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!

I have several drop down lists on a form and I need to run a query using the values from the drop down lists. I have created the query.

I want the user to be able to click on a button that will both run the query and show the results in an Excel spreadsheet.

Any suggestions will be greatly appreciated!
 
Sandy,

You need to run the TransferSpreadsheet function in the Click event of your
button. It will take the data returned by your query and export it to Excel.
Look in the Help file for the TransferSpreadsheet function.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Sandy said:
Hello!

I have several drop down lists on a form and I need to run a query using the
Click to expand...
values from the drop down lists. I have created the query.
I want the user to be able to click on a button that will both run the query
Click to expand...
and show the results in an Excel spreadsheet.
 
Hello!

I have several drop down lists on a form and I need to run a query using the values from the drop down lists. I have created the query.

I want the user to be able to click on a button that will both run the query and show the results in an Excel spreadsheet.

Any suggestions will be greatly appreciated!
Click to expand...

You can use the DoCmd.TransferSpreadsheet method, using the name of
the query and the name of the spreadsheet in the appropriate argument
positions.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName", "C:\My Household\SpreadsheetName.xls"

If there is a worksheet named "QueryName" the new data will override
the old. If there is no worksheet named "QueryName", Excel will create
one and you can then copy the data to whatever worksheet you wish.
 
Fred,

<< If there is a worksheet named "QueryName" >>

You mean - C:\My Household\SpreadsheetName.xls - don't you?

Steve
PC Datasheet
 
Hello again -

Well, I got that to work, but unfortunately the query returns more than 65,000 rows. I had forgotten Excel is whimpy in the amount of rows it can take.

I need the rows returned to be in a format that can be worked with further, i.e., make labels, use for form letters, etc. The users know how to operate Excel and Word for these purposes and my job is to get it out of Access.

Any ideas for a work-around?


P.S. Do you know if, in this new MSDN environment, when I post a reply it goes to everyone in the group?
 
Why not add criteria to your query to reduce the number of records exported to
Excel at any one time to less than 65,000. Perhaps you can group your data
somehow like by company.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Sandy said:
Hello again -

Well, I got that to work, but unfortunately the query returns more than 65,000
Click to expand...
rows. I had forgotten Excel is whimpy in the amount of rows it can take.
I need the rows returned to be in a format that can be worked with further,
Click to expand...
i.e., make labels, use for form letters, etc. The users know how to operate
Excel and Word for these purposes and my job is to get it out of Access.
 
That thought had occurred to me, but I'm not sure how to construct it. I already needed to do this and simply used Where ID Between 1 And 65000; then I picked up the rest using Where ID Between 65001 And 100000.

If someone is using the form, though, I am going to need some sort of If...Then to see if the result of the query is greater than 65000 and, if so, run two queries and two instances of Excel.

That's all that occurs to me at the moment. There has to be a better way . . .


Sandy
 
Fred,

<< If there is a worksheet named "QueryName" >>

You mean - C:\My Household\SpreadsheetName.xls - don't you?

Steve
PC Datasheet
Click to expand...

Actually, No.
Let's assume the spreadsheet workbook name is Sales.
Then the Path and File name is:
"C:\My Household\Sales.xls"
There is a worksheet in the "Sales" workbook is named
"LastMonthSales", then if the Access query is named "LastMonthSales"
it will override the existing data, or if there is no worksheet named
"LastMonthSales", create one.
 
Hello again -

Well, I got that to work, but unfortunately the query returns more than 65,000 rows. I had forgotten Excel is whimpy in the amount of rows it can take.

I need the rows returned to be in a format that can be worked with further, i.e., make labels, use for form letters, etc. The users know how to operate Excel and Word for these purposes and my job is to get it out of Access.

Any ideas for a work-around?

P.S. Do you know if, in this new MSDN environment, when I post a reply it goes to everyone in the group?
Click to expand...

Regarding >I need the rows returned to be in a format that can be
worked with further, i.e., make labels, use for form letters, etc.
The users know how to operate Excel and Word for these purposes and my
job is to get it out of Access. <

Access is a great platform for printing mailing labels and works well
with Word for use in form letters. Perhaps it's time for your users to
learn something new.
 
Back
Top