VBA Export query with filter to Excel

[papadega3000]

Hello,

I am trying to create an export function in Access to export a query with filter that is controlled by a combo box then export the query to Excel.

So far I browsed this forum and other sites and put together the VBA code that was used by other to try and accompolish what I want.

Private Sub ExportExcel_Click()
Dim StrSQL As String
Dim qdf As QueryDef

 
StrSQL = "Select * From [ExportQuery] Where [Run.Test_Case]= Forms![Export]![ComboExportTestCase]"
If DLookup("Name", "MSysObjects", "Name= 'ExportQuery'") <> "" Then
    Set qdf = CurrentDb.QueryDefs("ExportQuery")
    qdf.SQL = StrSQL
Else
    Set qdf = CurrentDb.CreateQueryDef("ExportQuery", StrSQL)
End If
DoCmd.OutputTo acQuery, "ExportQuery", "MicrosoftExcel(*.xls)", "", True, ""

'DoCmd.TransferSpreadsheet acExport, 8, "ExportQuery", "ChecklistExport", True, ""


End Sub

After I select it from the form and click the button i get a runtime error 3270 Property Not Found. I am not sure where in the line of code its refering too???
But the compilier points to the docmd line:

DoCmd.OutputTo acQuery, "ExportQuery", "MicrosoftExcel(*.xls)", "", True, ""

If anyone has tried this and has seen the error I'd be pleased to see how it is resolved.
I also zipped up a copy of the DB with some mock data so you can see it.

Thanks,

 

[georgedwilkinson]

I didn't really troubleshoot this or look at your sample but the thing that strikes me odd about the line of code that gave you an error is that you're exporting data to the file named *.xls. I don't believe Windows will allow you to name a file with the "*" character in the name. And since you cannot write to all .xls files at once, I would think this line of code would not work.

You might want to change that to a real .xls file name and try again.

 

[papadega3000]

Hello,

Thanks for the suggestion. However I just gave it a generic filename.xls and re-ran the code. I pull up with another run-time error #2282. The format you trying to output the current object is not available.

I am assuming that it either doesn't like my SQL string or the fact that I am trying to OutputTo a query. I am going to see if re-building the query with Access then generating a macro to transfer the query with TransferSpreadsheet will let me do what I wanted. My biggest concern is I want the filter to be applied to the query then export it.

Thanks for your input. Other suggestions are welcome.

 

[georgedwilkinson]

So, I did a little more research into your problem. Your "Export" query doesn't seem to work right. It causes a "circular reference", which may be related to it referencing a control on a form. I'm not sure if that's the problem.

 

[papadega3000]

Hello,

I have fixed my problems. It was with the query it was referencing itself. I also corrected the query in the VBA code. However, it works as I planned but the code doesn't pass the parameter to the query. It will prompt me for the parameter and the query executes but it doesn't returned the filtered result. It returns all of the records.
I attached the new db with the changes so you can see.

Thanks,

 

[papadega3000]

Hello,

I finally got back to working on this issue and I did resolve it. The parameter I was using did not exist in the query so I reworked the query to add the table with the value I wanted to export on as the parameter and it worked flawlessly.

Just thought I would post a reply since I got it working.

I changed my SQL string to this:

SELECT Task.*, Run.Test_Case
FROM Run INNER JOIN Task ON Run.Run = Task.Group
WHERE (((Run.Test_Case)=IIf(IsNull([Forms]![Export]![ComboExportTestCase]),[Run.Test_Case],[Forms]![Export]![ComboExportTestCase])));

 

[georgedwilkinson]

Gratz! Glad you got it running.

 

[adamrick]

Hi People!

I am new to the forums and a newbie at Access. I have been teaching myself Access on a 'need to do it in a crisis' basis and need some help with an annoying problem, very similar to the one above.

I have a simple query which I need to export to Excel. I have created the form which previews/prints the report nicely with the customer name as the filter as well as 'From' and 'To' dates applied. The problem is when I try to export the same report to Excel.

The export code below is pretty self explanatory, but I cannot work out how to filter the query before it exports to Excel. The code generates a second query "Customer History" with output from the original query "qryCustomerHistory" (as has been explained in similar forum threads) but I do not know how to filter the second query results based on the combo box "ClientName" and the "To" and "From" dates.

Attached is the database with just the firm and the two queries. The code I use for the command button "Export to Excel" is here:

Private Sub cmdExport_Click()
On Error GoTo btnImport_Click_error

   If Nz(Me![ExcelFile], "") = "" Then
      MsgBox "Please specify a path and file name in the box on the left", vbExclamation + vbOKOnly, "Can't Export"
      GoTo btnImport_Click_exit
   End If
   
   Dim s As String
   Dim strOR As String
   Dim Path As String
   Dim strCriteria As String
   Dim where As String
   Dim intLastSlash As Integer
   Dim sAnd As String
   Dim dbs As Database
   Dim qdf As QueryDef
   
     
   s = Dir(Me![ExcelFile])
   If s <> "" Then
        s = Me![ExcelFile]
        intLastSlash = GetLastInStr(s, "\")
        Path = Left(s, intLastSlash)
   End If
     
   DoCmd.SetWarnings False
      
   If strCriteria = "" Then
    where = ""
   Else
    where = " WHERE " & strCriteria
   End If
     
   s = "SELECT * FROM qryCustomerHistory" & where
   
   On Error Resume Next
   Set dbs = CurrentDb()
   dbs.QueryDefs.Delete "Customer History"
   
   On Error GoTo btnImport_Click_error
   Set qdf = dbs.CreateQueryDef("Customer History")
   qdf.SQL = s
   
   DoCmd.SetWarnings True
   
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Customer History", Me![ExcelFile], True
               
   lblCaption.Caption = "Finished exporting to Excel spreadsheet"
   lblCaption.Visible = True
   
btnImport_Click_exit:
Exit Sub

btnImport_Click_error:
Select Case Err
   Case 3010
      MsgBox "Excel file already open. Please close", vbExclamation + vbOKOnly, "Export Error"
   Case Else
      MsgBox "Excel file may be open. Please close", vbExclamation + vbOKOnly, "Export Error"
      Resume btnImport_Click_exit
End Select
End Sub

Please can some kind soul help me solve this filter issue? I have a headache after 2 weeks of blindly searching...! Thanks!

 

[adamrick]

Database did not attach.... trying again..!

 

[DCrake]

Adam

Her is a revised version of your database. Take a look at the changes I have made. If you need more insight into what I have done I suggest you visit the sample databases forum and look for a demo I posted about passing variables between forms and reports/queries. It comes with full documentation.

David

 

[adamrick]

Dave,

You are a star!! Thanks for solving my problem!!!

All Kudos to you Mate!