Exporting query to Excel causes SQL to be deleted! (1 Viewer)

thardyjackson

Registered User.
Local time
Yesterday, 19:13
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:
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
 

Guus2005

AWF VIP
Local time
Today, 03:13
Joined
Jun 26, 2007
Messages
2,645
Not sure why you dimension all arguments as Variant, when you clearly only pass strings?
Remove the command Docmd.Setwarnings False, if you want to catch any messages which might give you a clue as to what is happening.

If you want to export a query to Excel, the following procedure is a lot quicker.
It uses the Excel method: CopyFromRecordset

Enjoy!
Code:
Public Function CreateSpreadsheetFromRS(wsExcel As Worksheet, _
                                            rst As DAO.Recordset, _
                            Optional blnAutofit As Boolean = True, _
                             Optional blnHeader As Boolean = True, _
                                Optional intRow As Integer = 1, _
                                Optional intCol As Integer = 1) As Boolean
    Dim iCols  As Integer
    Dim strRng As String
    
    'Assume success
    CreateSpreadsheetFromRS = True
    
    On Error GoTo Err_CreateSpreadsheetFromRS
        
    If blnHeader Then
        strRng = Chr(64 + intCol) & intRow + 1
        For iCols = 0 To rst.Fields.Count - 1
            wsExcel.Cells(intRow, iCols + intCol).Value = rst.Fields(iCols).Name
        Next
        wsExcel.Range(strRng).CopyFromRecordset rst
    Else
        strRng = Chr(64 + intCol) & intRow
        wsExcel.Range(strRng).CopyFromRecordset rst
    End If
    
    If blnAutofit Then
        wsExcel.Columns.AutoFit
        wsExcel.Rows.AutoFit
    End If
    
Exit_CreateSpreadsheetFromRS:
    Exit Function

Err_CreateSpreadsheetFromRS:
    Select Case Err
    Case -2147467259
    Case Else
        MsgBox "Error " &  Err & "," Error$
    End Select
    CreateSpreadsheetFromRS = False
    Resume Exit_CreateSpreadsheetFromRS

End Function
 

flashgordon

New member
Local time
Yesterday, 19:13
Joined
Aug 8, 2013
Messages
1
thardyjackson

There appears to be a bug in Access; the "DoCmd.OutputTo acOutputQuery" changes the select clause.

A snippet of code that may help:
'DoCmd.OutputTo acOutputQuery, objectName, "ExcelWorkbook(*.xlsx)", filePath & outputFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel_, objectName, filePath & outputFileName, True
'change acSpreadsheetTypeExcel to the appropriate value for your system

Dim objXL As Object
Set objXL = GetObject(, "Excel.Application")
Set objXL = GetObject(filePath & outputFileName)
objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True
 
Last edited:

Users who are viewing this thread

Top Bottom