Error 3828 when exporting queries to Excel (1 Viewer)

[arnelgp]

here again with some modifications.
before running the code, Kill Excel from Task Manager(Ctrl-Shit-ESC), then find Excel.
Right-click on the Excel Process, the choose End Task.

 

[ChrisMore]

here again with some modifications.
before running the code, Kill Excel from Task Manager(Ctrl-Shit-ESC), then find Excel.
Right-click on the Excel Process, the choose End Task.

With these changes I'm getting run-time error 3061 Too few parameters. Expected 1.

I tried running the code with your attachment and can see it works but not with my setup currently.

Still not sure whether I should be including SalesOrder_Query in the below because if not I get error 3828 again, but it's not in your code.

If arrQueries(i) = "SalesOrder_Query" Or arrQueries(i) = "SalesInvoice_Query" Or arrQueries(i) = "PurchaseOrder_Query" Then

 

[arnelgp]

this is very fast, total re-code.
i remove the module and instead, change the query string if there is parameter.
see the code on the button

 

[ChrisMore]

this is very fast, total re-code.
i remove the module and instead, change the query string if there is parameter.
see the code on the button

I really appreciate your time to create this code but this time I'm back with the error 3828. Your approach from post #17 is the closest it's been to working for me.

 

[Gasman]

Worked for me?, so examine what is different with what @arnelgp supplied

Remove the .txt extension

 

[Ken Sheridan]

Thank you for the suggestion, however my lack of knowledge in the terminology you've used is holding me back in understanding what you mean. Are you able to simplify what you said or provide an example of what you mean?

The following is a simple example (untested) which replaces a parameter referencing a txtDateApponted parameter with the value of the parameter:

    Dim strSQL_Current  as String
    Dim strSQL_Temp As String
    Dim qdf As DAO.Querydef

    ' replace queries SQL statement , substituting  a date literal for the parameter
    Set qdf = CurrentDb.querydefs("QueryNameGoesHere")
    strSQL_Current = qdf.SQL
    strSQL_Temp = Replace(trSQL_Current,"Forms!frmEmployees!txtDateAppointed", "#" & Format(Me.txtAppointed,"yyyy-mm-dd") & "#")
    qdf.SQL = strSQL_Temp

    ' export query's result table to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryNameGoesHere", strFilePath, True

    ' reinstate query's original SQL property
    qdf.SQL = strSQL_Current

Rather than having separate code for each query, however, it would be better to wrap the code in a function in the form's module, into which the name of the query, the parameter, the date value, and the path to the Excel file could be passed as arguments. The function could then be called for each relevant query.

 

[Gasman]

Ken, that is what @arnelgp's code does.