thardyjackson
Registered User.
- Local time
- Today, 04:54
- Joined
- May 27, 2013
- Messages
- 45
I remember hearing about this issue. It's happening to me. I wrote a SQL query that works fine. But when I call a custom function to export it to Excel, my query SQL will get deleted (not consistent). All that is left of the script is SELECT; Anyone know why this is happening and how to avoid it? Thanks. MS Access 2007, Windows 7.
Here's how I call the function:
Here's the custom function:
Here's how I call the function:
Code:
Private Sub exportQryCustStatusAnalysisLite_Click()
Call ExcelSmsOutput("Query", "qry_custStatusAnalysisExport", "qry_custStatusAnalysisExport", Forms![frm_main].[filePath])
End Sub
Here's the custom function:
Code:
Public Function ExcelSmsOutput(objectType As Variant, prefixFileName As Variant, objectName As Variant, filePath As Variant)
Dim outputFileName As String
outputFileName = prefixFileName & " " & Format(Now(), "yyyy-mm-dd hh\h mm\m ss\s")
DoCmd.SetWarnings False
If objectType = "table" Then
DoCmd.OutputTo acOutputTable, objectName, "ExcelWorkbook(*.xlsx)", filePath & outputFileName & ".xlsx"
MsgBox outputFileName & ".xlsx has been generated."
ElseIf objectType = "query" Then
DoCmd.OutputTo acOutputQuery, objectName, "ExcelWorkbook(*.xlsx)", filePath & outputFileName & ".xlsx"
MsgBox outputFileName & ".xlsx has been generated."
Else
DoCmd.OutputTo acOutputReport, objectName, acFormatPDF, filePath & outputFileName & ".pdf"
MsgBox outputFileName & ".pdf has been generated."
End If
DoCmd.SetWarnings True
End Function