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
6 6224 PEB 1,418
Expert 1GB
Hi,
Simply set up those filters directly in your query instaed of your reports
:)
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
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. - 'ExportToExcel exports recordset {strRS} to {strFolder}\{strName}.Xls
-
'Returns the eventual file name used
-
Public Function ExportToExcel(strName As String, _
-
Optional ByVal strRS As String = "", _
-
Optional ByVal strWhere As String = "", _
-
Optional ByVal strFolder As String = "") As String
-
Dim strOut As String, strFile As String, strType As String
-
Dim objRS As Object
-
-
'If no RecordSet supplied assume "tbl" & strName
-
If strRS = "" Then strRS = "tbl" & strName
-
'Determine the DataSet type (strType) - (T)able; (Q)uery; (S)QL
-
On Error Resume Next
-
Set objRS = CurrentDb.TableDefs(strRS)
-
If Not objRS Is Nothing Then
-
strType = "T"
-
Else
-
Set objRS = CurrentDb.QueryDefs(strRS)
-
strType = IIf(objRS Is Nothing, "S", "Q")
-
End If
-
On Error GoTo 0
-
'Code to insert a WHERE clause into the SQL from strWhere
-
If strWhere > "" Then
-
strRS = ParamReplace("SELECT * FROM (%S)%A WHERE (%W)", _
-
"%S", strRS, _
-
"%A", IIf(strType = "S", " AS subQ", ""), _
-
"%W", strWhere)
-
strType = "S"
-
End If
-
'If SQL string to be used - create temp query
-
If strType = "S" Then _
-
Set objRS = TempQuery(strName:=strName, strSQL:=strRS, blnCopy:=True)
-
'If no folder supplied, use the 'Temp' folder
-
If strFolder = "" Then strFolder = Environ("Temp")
-
'For uniformity, remove any trailing "\" from folder name
-
If Right(strFolder, 1) = "\" Then _
-
strFolder = Left(strFolder, Len(strFolder) - 1)
-
strOut = strFolder & "\" & strName & "New.Xls"
-
strFile = strFolder & "\" & strName & ".Xls"
-
'Delete temp file we will use, if it exists - if not, then not a problem
-
On Error Resume Next
-
Call Kill(strOut)
-
On Error GoTo 0
-
'Export to temp file - if this works then rename to required file
-
strName = objRS.Name
-
Set objRS = Nothing
-
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
-
TableName:=strName, _
-
FileName:=strOut)
-
On Error Resume Next
-
Call Kill(strFile)
-
On Error GoTo 0
-
Name strOut As strFile
-
If strType = "S" Then Call CurrentDb.QueryDefs.Delete(Name:=strName)
-
ExportToExcel = strFile
-
End Function
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
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).
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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"...
|
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...
|
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).
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |