Copy Query to Excel

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

Guest

Hi,

Can anyone help me please? I have a query, and I want to press a button
every day which will run the query and copy the results to an Excell file and
save it on the C drive. an added bonus would be if the File name had a date
on it, so that it wouldnt overwrite yesterdays dump.

Thankyou very much for your help i really appreciate it

cheers!
 
I use:

MyVal = Format(Now(), "ddmmyy")

DoCmd.TransferSpreadsheet acExport,
0, "YourSQL, "C:\Temp\YourFilename & MyVal & ".xls", True
MsgBox "The YourFilename.xls file has been placed in
the C:\Temp folder on this pc"

If you want to have a date in the file name the filename
will always be different each day so will not overwrite
the previous file
 
Private Sub Command0_Click()

Dim strPath As String
Dim strFileName As String
Dim strPathName As String

'Saves workbook in same folder as MDB.
strPath = CurrentProject.Path
strFileName = Format$(Date, "yyyymmdd") & ".xls"
strPathName = strPath & "\" & strFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, _
"qryTest", strPathName


End Sub


--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top