By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,066 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,066 IT Pros & Developers. It's quick & easy.

Export a report to excel that uses a query and additional filters.

P: 3
I have a report that opens up using a pretty complex query. I then pop up a form with combo boxes so the user can apply a filter to the existing report and then refresh the report. I need to export the report's records with the applied query to excel in datasheet form and it needs to contain the additional filters that the user selects. The reports datasource is the original query.

Right now I have this and get the datasheet format, but I need the filters that were passed to the report - how can I export the query with the additional filters?

'Export the query as a datasheet in excel - missing filters from user
DoCmd.OutputTo acOutputQuery, "BOM_QTY_Status_qry", acFormatXLS, strFileName, True

Thanks
Oct 27 '06 #1
Share this Question
Share on Google+
6 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,

Simply set up those filters directly in your query instaed of your reports

:)
Oct 27 '06 #2

pks00
Expert 100+
P: 280
Is it possible to export your report in excel?
I mean run your report in preview mode then analyze it with excel

e.g


DoCmd.OpenReport "MyReport", acViewPreview
DoCmd.RunCommand acCmdOutputToExcel
Oct 30 '06 #3

NeoPa
Expert Mod 15k+
P: 31,429
It is possible, but crap ;).

Not entirely, but the resultant spreadsheet has data in columns all over the place and is hard work to format into a useful document.

I am working on a similar job ATM and I decided to implement, in code, the application of the various filters into the underlying recordsource of the Report.

It doesn't use the data already run for the report.
I'll copy in the routine I'm using for ideas, but it uses functions you won't have available so it won't work for you directly. Even this one routine is quite large. I will however dig up the other stuff if requested - I don't wanna post a load of code here that no-one wants. I myself am always very cautious about using code developed elsewhere.

Expand|Select|Wrap|Line Numbers
  1. 'ExportToExcel exports recordset {strRS} to {strFolder}\{strName}.Xls
  2. 'Returns the eventual file name used
  3. Public Function ExportToExcel(strName As String, _
  4.                               Optional ByVal strRS As String = "", _
  5.                               Optional ByVal strWhere As String = "", _
  6.                               Optional ByVal strFolder As String = "") As String
  7.     Dim strOut As String, strFile As String, strType As String
  8.     Dim objRS As Object
  9.  
  10.     'If no RecordSet supplied assume "tbl" & strName
  11.     If strRS = "" Then strRS = "tbl" & strName
  12.     'Determine the DataSet type (strType) - (T)able; (Q)uery; (S)QL
  13.     On Error Resume Next
  14.     Set objRS = CurrentDb.TableDefs(strRS)
  15.     If Not objRS Is Nothing Then
  16.         strType = "T"
  17.     Else
  18.         Set objRS = CurrentDb.QueryDefs(strRS)
  19.         strType = IIf(objRS Is Nothing, "S", "Q")
  20.     End If
  21.     On Error GoTo 0
  22.     'Code to insert a WHERE clause into the SQL from strWhere
  23.     If strWhere > "" Then
  24.         strRS = ParamReplace("SELECT * FROM (%S)%A WHERE (%W)", _
  25.                              "%S", strRS, _
  26.                              "%A", IIf(strType = "S", " AS subQ", ""), _
  27.                              "%W", strWhere)
  28.         strType = "S"
  29.     End If
  30.     'If SQL string to be used - create temp query
  31.     If strType = "S" Then _
  32.         Set objRS = TempQuery(strName:=strName, strSQL:=strRS, blnCopy:=True)
  33.     'If no folder supplied, use the 'Temp' folder
  34.     If strFolder = "" Then strFolder = Environ("Temp")
  35.     'For uniformity, remove any trailing "\" from folder name
  36.     If Right(strFolder, 1) = "\" Then _
  37.         strFolder = Left(strFolder, Len(strFolder) - 1)
  38.     strOut = strFolder & "\" & strName & "New.Xls"
  39.     strFile = strFolder & "\" & strName & ".Xls"
  40.     'Delete temp file we will use, if it exists - if not, then not a problem
  41.     On Error Resume Next
  42.     Call Kill(strOut)
  43.     On Error GoTo 0
  44.     'Export to temp file - if this works then rename to required file
  45.     strName = objRS.Name
  46.     Set objRS = Nothing
  47.     Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
  48.                                    TableName:=strName, _
  49.                                    FileName:=strOut)
  50.     On Error Resume Next
  51.     Call Kill(strFile)
  52.     On Error GoTo 0
  53.     Name strOut As strFile
  54.     If strType = "S" Then Call CurrentDb.QueryDefs.Delete(Name:=strName)
  55.     ExportToExcel = strFile
  56. End Function
Oct 30 '06 #4

P: 3
To export a report to Excel is messy looking. Export a query instead, it exports in datasheet form. What I did was create a query that the report uses and a button on a form that says 'Export to Excel'. On the click event I call the query for the report being displayed and it has the command:

DoCmd.OutputTo acOutputQuery, "query_name_here", acFormatXLS, "path_and_file_name_here", True
Oct 31 '06 #5

NeoPa
Expert Mod 15k+
P: 31,429
I'm doing a lot of work on exporting to Excel in my main database at work ATM.
I wasn't aware of that construct - I'll check into it tomorrow in case it's better than what I'm currently using (DoCmd.TransferSpreadsheet).
Oct 31 '06 #6

P: 1
My name is hunter bowker.
came here through google.
this forum seems to be ranking high on google.
btw what is the most busiest time on this forum so that i could interact with peak members or is it ever this lonely.
Sep 21 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.