Access 2007 Qry to Excel 2007

[gsbatch1]

RESOLVED -- Access 2007 Qry to Excel 2007

I know this has probably been asked and answered multiple times before, I have spent the better part of the day trying to find an answer to this, even searching on here.

I have a query that I want to export as raw data. I have an email aspect that sends out the data in the body of the email. I want to be able to, once the email portion is done, save the raw data as and Excel file and add the current date to the file name.( XXXXX.02222011) This is so trending can be performed weekly on the data pulled daily.

I can create a macro and have it export the Query results, but then I get this crazy error (Repaired Records: Format from /xl/styles.xml part (Styles)
) when I try to run this macro.

I usually create a Macro, and then convert it to VBA and learn what it does as my way of teaching myself how to write the code.

Any help would be much appreciated.

 

[SpentGeezer]

Will this do the trick?

Dim strFilePath as string
strFilePath replace(NOW," ","")
strFilePath replace(strFilePath,":","")
strFilePath replace(strFilePath,"/","")
strfilepath = "c:\"My_Report_Name_" &  strfilepath & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_My_Query_Name", strfilepath, True

 

[gsbatch1]

Nope, Sorry did not work. All it does is sit there. I use an OnClick event and it does nothing.

 

[SpentGeezer]

Nope, Sorry did not work. All it does is sit there. I use an OnClick event and it does nothing.

It does nothing? I find that hard to believe. What do you mean it "sits there". Have you checked the directory for a saved excel file. I will post an example......

 

[SpentGeezer]

command button code:

Private Sub Command0_Click()
Dim strFilePath As String
strFilePath = Replace(Now, " ", "")
strFilePath = Replace(strFilePath, ":", "")
strFilePath = Replace(strFilePath, "/", "")
strFilePath = "c:\Report" & strFilePath & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", strFilePath, True
End Sub

example:

 

[gsbatch1]

Sorry I did not catch it sooner, but I just saw why your original code did not work.

Dim strFilePath as string
strFilePath replace(NOW," ","")
strFilePath replace(strFilePath,":","")
strFilePath replace(strFilePath,"/","")
strfilepath = "c:\"My_Report_Name_" & strfilepath & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_My_Query_Name", strfilepath, True
There was that additional " before My Report

Without it, it worked fine. My bad for not catching it sooner.