473,322 Members | 1,314 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
6 6224
PEB
1,418 Expert 1GB
Hi,

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

:)
Oct 27 '06 #2
pks00
280 Expert 100+
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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

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

Similar topics

1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
2
by: PerryC | last post by:
Is there a way to export an MS Access Report to Word/Excel that looks EXACTLY like it appears in Access? When I export to Excel, only certain data appears, titles, headings... all missing. The...
14
by: bonehead | last post by:
Greetings, I'm using the DoCmd.TransferText method to export the results of a MS Access query to a csv file. The csv will then be used to load an Oracle table. In other systems such as TOAD...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
1
by: JawzX01 | last post by:
Hello All, First, thank you for any help you can provide. I'm trying to do a simple export to excel. I've used the classic code that is all over the internet, and of course it worked without a...
3
by: yovation | last post by:
Hi, I have a 3 table database. 1 parent 1 child 1 child of child I would like to get the data into excel as 1 sheet (similar to a grouped report).
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
7
by: barrhaven01 | last post by:
Hi there, I was wondering if anyone could suggest a method using only access ( ie no additional applications) to batch export reports to excel. Basically i have a report with a query for its...
1
by: chandhseke | last post by:
Hi folks, I am a newbie to this functionality of creating excel report using VB/VB Script. I have been given a code and asked to add filters to the excel sheet that is created using the below...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.