Export to Excel with filters

[Danick]

I'm trying to create a button that will export the filtered records on the screen to an Excel file.

I'm using strWhere as my where string and found this code in one of the posts from this forum, but unfortunately, I can't get it output only the filtered records. It outputs all records instead.


Dim db As dao.Database, qdf As QueryDef, mySQL As String
Dim strWHERE As String

Const strSQL = "SELECT * FROM [Action Register] "

Set db = CurrentDb
Set qdf = db.QueryDefs("qryActionItems")
qdf.SQL = strSQL & "strWhere;"


Set qdf = Nothing
Set db = Nothing

DoCmd.OutputTo acOutputQuery, "qryActionItems", acFormatXLS, CurrentProject.Path & "\" & "Action Items.xls", True

 

[vbaInet]

Something like this (post #10):

http://www.access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto

 

[Danick]

Something like this (post #10):

http://www.access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto

Not exactly, I'm following your example in post #3 here:

http://www.access-programmers.co.uk/forums/showthread.php?t=190439&highlight=acOutputQuery

But just can't get it to work

 

[vbaInet]

I didn't notice that you're outputting a query.

Ok, so where is the criteria? You've not applied it properly or set it anywhere.

 

[Danick]

I didn't notice that you're outputting a query.

Ok, so where is the criteria? You've not applied it properly or set it anywhere.

I'm trying to output a query called "qryActionItems" with a filter called "strWhere".

The button does output the file, but the "strWhere" filter is ignored and don't know how to get it in the code.

I've also tried using

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,...

But that didn't work either. Seems I can't get dynamic filters to work when trying export to Excel...

 

[vbaInet]

Yes I noticed after a second look.

strWhere is being ignore because it's seen as a string and not a variable so remove the double quotes. But you've not shown where strWhere is being set?

Also, three more things to do:
1. Save the SQL of the query in a temporary variable before you change it
2. After exporting the query, change the SQL of the query back to the original SQL saved in the variable in step 1
3. Ensure you have error handling in place that would put the SQL of the query back to its original state if it errors after it was changed.

 

[Danick]

Yes I noticed after a second look.

strWhere is being ignore because it's seen as a string and not a variable so remove the double quotes. But you've not shown where strWhere is being set?

Also, three more things to do:
1. Save the SQL of the query in a temporary variable before you change it
2. After exporting the query, change the SQL of the query back to the original SQL saved in the variable in step 1
3. Ensure you have error handling in place that would put the SQL of the query back to its original state if it errors after it was changed.

I'm using a similar button to output the query with strWhere clause to a report. So I know the strWhere clause is working fine. I thought I could use the exact same code and just change the output to an excel file instead of a report.


I got rid of the double quotes so it looks like this:

qdf.SQL = strSQL & strWHERE

But now I get a runtime error code 3131 syntax error in FROM clause.

 

[vbaInet]

Your SQL string is not well formed. Debug.Print the entire SQL string and you'll see your mistake.

 

[JHB]

I got rid of the double quotes so it looks like this:

qdf.SQL = strSQL & strWHERE

But now I get a runtime error code 3131 syntax error in FROM clause.

The variable strWHERE is empty, because you haven't apply anything to it. You declare it in code line #2, "Dim strWHERE As String".

 

[vbaInet]

The variable strWHERE is empty, because you haven't apply anything to it. You declare it in code line #2, "Dim strWHERE As String".

I asked highlighted this same point, but Danick's response was:

I'm using a similar button to output the query with strWhere clause to a report. So I know the strWhere clause is working fine.

Maybe Danick is doing it somewhere that we don't see

 

[Danick]

I asked highlighted this same point, but Danick's response was:
Maybe Danick is doing it somewhere that we don't see

Thanks all, yes the strWhere was being declared but didn't show it here.
In the end I found this too complicated and decided to try and just create a simple query and use the text boxes in the criteria like this:

Like "*" & [Forms]![frmSummary].[Form]![cboCustomer] & "*"

I've added these to all the fields that the user may be trying to filter for.
It's not as pretty, but seems to be getting the job done.

One problem I'm getting doing it this way, is that I'm getting a run time error 2302 when the user has the excel file opened. This is to be expected, but I don't like that the user could hit "debug" and go into the VBA screen. And since I'm now using a simple query form, I'm not sure how to prevent it...

 

[vbaInet]

It's pretty straightforward but at least you thought out a different method.

If you had error handling in place then your users would not see Debug. You trap the error and display a custom message asking the user to close the file, or give them an option of saving the file with a different name.

 

[Spidergoy]

The correct way to code the clause qdf.SQL = strSQL & "strWhere;" would be:

qdf.SQL = strSQL & "'" & strWhere & "'"

assuming the string strWhere has been defined somewhere.