Trouble exporting a parameter query to Excel 1

[Stadler09]

Hello. I am trying to export a select query that references a crosstab query to Excel. I have the VBA code that exports regular queries and I'm using that code to automate the exporting process.

I have a form that supplies a specific date range for the user and I want to query my crosstab query for data within that date range. I have set the criteria for the date range, and have supplied the appropriate parameters in the parameter settings for the query. Yet when I try to export, it gives me the following error...

Run-time error '3061':
Too few parameters. Expected 1.

I have no problem exporting select queries that reference crosstab queries, but when I set criteria that refers to the date range on the form, I get the error. Any ideas?

 

[farmera]

Thanks all for the help! The GetFormValue worked for the arguments error.

I'm having another issue since I included all of the queries in the code: "Object variable or With block variable not set."

It happens while posting the second page to the spreadsheet. The exact point is in bold below:

Option Compare Database

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

  Dim objExcel As New Excel.Application
  With objExcel
   .Workbooks.Add 'adds a new workbook
   .DisplayAlerts = False
   .Visible = True 'Can remove if users don't need to see what is happening
   
   While .Sheets.Count > 1 'Remove all of the sheets in the workbook
    .Sheets(1).Delete
   Wend
   
   'Create workbook sheets to match queries to be exported
   AddSheet objExcel, "BCDES_LesInfluent", "BCDES_LesInfluent", "BCDES_LesInfluent", .Sheets(1)
   AddSheet objExcel, "BCDES_LesEffluent", "BCDES_LesEffluent", "BCDES_LesEffluent"
   AddSheet objExcel, "BCDES_LesDownstream", "BCDES_LesDownstream", "BCDES_LesDownstream"
   AddSheet objExcel, "BCDES_LesUpstream", "BCDES_LesUpstream", "BCDES_LesUpstream"
   AddSheet objExcel, "BCDES_LesSolidsCake", "BCDES_LesSolidsCake", "BCDES_LesSolidsCake"
   AddSheet objExcel, "BCDES_LesSolidsDig1", "BCDES_LesSolidsDig1", "BCDES_LesSolidsDig1"
   AddSheet objExcel, "BCDES_LesSolidsDig2", "BCDES_LesSolidsDig2", "BCDES_LesSolidsDig2"
   AddSheet objExcel, "BCDES_LesSolidsDig3", "BCDES_LesSolidsDig3", "BCDES_LesSolidsDig3"
   AddSheet objExcel, "BCDES_LesSolidsDig4", "BCDES_LesSolidsDig4", "BCDES_LesSolidsDig4"
   AddSheet objExcel, "BCDES_LesSolidsSBT", "BCDES_LesSolidsSBT", "BCDES_LesSolidsSBT"
   AddSheet objExcel, "BCDES_LesSolids2MGD", "BCDES_LesSolids2MGD", "BCDES_LesSolids2MGD"
   AddSheet objExcel, "BCDES_LesSolids6MGD", "BCDES_LesSolids6MGD", "BCDES_LesSolids6MGD"
   AddSheet objExcel, "BCDES_LesMonthlyCake", "BCDES_LesMonthlyCake", "BCDES_LesMonthlyCake"
   AddSheet objExcel, "BCDES_UMCInfluent", "BCDES_UMCInfluent", "BCDES_UMCInfluent"
   AddSheet objExcel, "BCDES_UMCEffluent", "BCDES_UMCEffluent", "BCDES_UMCEffluent"
   AddSheet objExcel, "BCDES_UMCDownstream", "BCDES_UMCDownstream", "BCDES_UMCDownstream"
   AddSheet objExcel, "BCDES_UMCUpstream", "BCDES_UMCUpstream", "BCDES_UMCUpstream"
   AddSheet objExcel, "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake"
   AddSheet objExcel, "BCDES_UMCSolidsDig1", "BCDES_UMCSolidsDig1", "BCDES_UMCSolidsDig1"
   AddSheet objExcel, "BCDES_UMCSolidsDig2", "BCDES_UMCSolidsDig2", "BCDES_UMCSolidsDig2"
   AddSheet objExcel, "BCDES_UMCSolidsDig3", "BCDES_UMCSolidsDig3", "BCDES_UMCSolidsDig3"
   AddSheet objExcel, "BCDES_UMCSolidsDig4", "BCDES_UMCSolidsDig4", "BCDES_UMCSolidsDig4"
   AddSheet objExcel, "BCDES_UMCSolidsDitch1", "BCDES_UMCSolidsDitch1", "BCDES_UMCSolidsDitch1"
   AddSheet objExcel, "BCDES_UMCSolidsDitch2", "BCDES_UMCSolidsDitch2", "BCDES_UMCSolidsDitch2"
   AddSheet objExcel, "BCDES_UMCMonthlyCake", "BCDES_UMCMonthlyCake", "BCDES_UMCMonthlyCake"
   AddSheet objExcel, "BCDES_AlamoInfluent", "BCDES_AlamoInfluent", "BCDES_AlamoInfluent"
   AddSheet objExcel, "BCDES_AlamoEffluent", "BCDES_AlamoEffluent", "BCDES_AlamoEffluent"
   AddSheet objExcel, "BCDES_AlamoSolidsDownstream", "BCDES_AlamoSolidsDownstream", "BCDES_AlamoSolidsDownstream"
   AddSheet objExcel, "BCDES_AlamoSolidsUpstream", "BCDES_AlamoSolidsUpstream", "BCDES_AlamoSolidsUpstream"
   AddSheet objExcel, "BCDES_QueenAcresInfluent", "BCDES_QueenAcresInfluent", "BCDES_QueenAcresInfluent"
   AddSheet objExcel, "BCDES_QueenAcresEffluent", "BCDES_QueenAcresEffluent", "BCDES_QueenAcresEffluent"
   AddSheet objExcel, "BCDES_QueenAcresDownstream", "BCDES_QueenAcresDownstream", "BCDES_QueenAcresDownstream"
   AddSheet objExcel, "BCDES_QueenAcresUpstream", "BCDES_QueenAcresUpstream", "BCDES_QueenAcresUpstream"
   AddSheet objExcel, "BCDES_WadeMillInfluent", "BCDES_WadeMillInfluent", "BCDES_WadeMillInfluent"
   AddSheet objExcel, "BCDES_WadeMillEffluent", "BCDES_WadeMillEffluent", "BCDES_WadeMillEffluent"
   AddSheet objExcel, "BCDES_WadeMillDownstream", "BCDES_WadeMillDownstream", "BCDES_WadeMillDownstream"
   AddSheet objExcel, "BCDES_WadeMillUpstream", "BCDES_WadeMillUpstream", "BCDES_WadeMillUpstream"
   AddSheet objExcel, "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake", "BCDES_UMCSolidsCake"
   AddSheet objExcel, "BCDES_NewMiamiVillage", "BCDES_NewMiamiVillage", "BCDES_NewMiamiVillage"
   AddSheet objExcel, "BCDES_NewMiamiEffluent", "BCDES_NewMiamiEffluent", "BCDES_NewMiamiEffluent"
   
   'Save the workbook
   .ActiveWorkbook.SaveAs "C:\temp\Results.xls"
    
    'Close the workbook
    .ActiveWorkbook.Close
    .DisplayAlerts = True
    .Quit 'Quit Excel
   End With
   
   'Release the object
   Set objExcel = Nothing
   
   MsgBox "Completed Export - File saved to C:\temp\Results"
Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
End Sub
   Private Function AddSheet(ByRef objExcel As Excel.Application, _
   ByVal sSheetName As String, ByVal sTitle As String, ByVal sSQL As String, _
   Optional ByRef objSheet As Excel.Worksheet)
   
   Dim objSht As Excel.Worksheet
   Dim objRecordset As Recordset
   Dim nCount As Integer
   
   If objSheet Is Nothing Then
     Set objSheet = objExcel.Sheets.Add
   Else
     Set objSht = objSheet
   End If
   
   'Getting the data to import into the worksheet
   Set objRecordset = CodeDb.OpenRecordset(sSQL)
   
   'Paste the data into the sheet
   [b]With objSht
     .Cells(4, 1).CopyFromRecordset objRecordset[/b]
     
    'Add the Field names
    For nCount = 0 To objRecordset.Fields.Count - 1
    .Cells(3, 1 + nCount).Formula = objRecordset.Fields(nCount).Name
    .Cells(3, 1 + nCount).Font.Bold = True
    Next nCount
    
    'Automatically adjust column width for data
    .Columns.AutoFit
    
    'Add the Title
    .Cells(1, 1).Formula = sTitle
    .Cells(1, 1).Font.Bold = True
    .Cells(1, 1).Font.Size = 14
    
    'Set the sheet name
    .Name = sSheetName
    
    End With
    
    Set objRecordset = Nothing
    End Function

Please forgive this newbie for being a pest!

Angie

 

[farmera]

Got it! Roy-Vidar gave me this hint:

Instead of Set objSheet, change to:

If objSheet Is Nothing Then
     Set objSht = objExcel.Sheets.Add
   Else
     Set objSht = objSheet
   End If

 

[farmera]

I need to use a wildcard in the criteria. For example, if the user enters "farmer", I need the query to return all records that contain "farmer" as the first 6 characters. Is there a way to use a wildcard similar to the "like F*" used in the qbe with the GetFormValue function?

I am asking users to enter the last name in txtName then I'm adding

Me!txtName = Me!txtName + "*"

to the module, but that is not working with the GetFormValue("txtName").

Thanks in advance for your help!

 

[PHV]

Use Like GetFormValue("txtName")[/i] in your SQL code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[farmera]

Here is my SQL

SELECT qryAssessmentCalc.Name, qryAssessmentCalc.PStr, qryAssessmentCalc.Expr1, qryAssessmentCalc.Page, qryAssessmentCalc.Parcel, qryAssessmentCalc.ParcelNu, qryAssessmentCalc.ServiceLoc
FROM qryAssessmentCalc
WHERE ([b]Like GetFormValue("txtname")[/i]);

I'm getting a missing operator error?

 

[PHV]

WHERE qryAssessmentCalc.Name Like GetFormValue("txtname");


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886