tsansferspreedsheet command to do more??

[juniormint2009]

Hi all, after I run some of mu queries from a command button with the DoCmd.openquery command, I use the Do.cmd transferspreadsheet so it goes right to and excel file. I would like to do more though.
Can I have it name the file something unique so the user knows to look for it? Like adding something like +date() to the filename being outputed part?
Also, I have a second query that gives some metrics on the past query like taking a field, grouping it by that field and then using an aggregat function to compute the average number of values on that field.
Can I have that query's results be put into the same excel sheet as the previous but in a seperate sheet so they only have to open 1 excel file and have all the records in 1 sheet and then the second sheet having the metrics (all the group by fields)
Thanks again!!!

 

[HarleyQuinn]

You can do both.

You can easily add the date to a filename you're passing and you can (seemingly contrary to the MSHelp file) specify a sheet in the the TransferSpreadsheet's Range parameter. Here's an example of using both:

Private Sub Command0_Click()
Dim strOutPutFile As String

strOutPutFile = "C:\MyFile" & Format(Date, "yyyymmdd") & ".xls" ' create filename

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourTable", strOutPutFile, True, "Sheet1"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourTable", strOutPutFile, True, "Sheet2"

Hope this helps

End Sub

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[juniormint2009]

WOW, that is awesome! Thank you Harley!!! I have it working great now! Thanks again!

 

[juniormint2009]

This is great! Really saves me time from cutting and pasting, another question, is there a way to name the sheets something else besides "sheet 1", "sheet 2" etc ??? I want to be able name the sheets a certain name, can this be done??
Also, one of my queries has a field that I calculate and is formtaed to be a percent, when it goes over to excel it is in decimal point number style like .02 or .28, can I have it go over to excel formated like that ??