Export to Excel Spreadsheet Using Filter

  • Thread starter Thread starter Joe R
  • Start date Start date
J

Joe R

I would like to create an Excel spreadsheet using a query
and filter from the output of a drop down menu (fed by one
of the database fields) on the form. I can do this with
the OpenReport command, but have not found a way to do
this using the TransferSpreadsheet function. Neither do I
know another way to export results to a spreadsheet other
than the TransferSpreadsheet function.

Is there a way I can filter output that is exported to an
Excel spreadsheet? Also, can I designate the tabs names?
 
Create a macro that runs the query that creates a
temporary table and use the transferspreadsheet option to
export the temporary table to Excel

Jim
 
The filtering will be done in the query. For example if
you only wanted a specific date range in the criteria
under date in your query you would enter between [Enter
Date 1] and [Enter Date2]. When you run the query you
will be promted for Date1 and Date 2.

Jim
 
What I would like to do is filter from a field in the form
I am using. The following code is behind a button on the
form, frmMenu, which produces a filtered report:

Private Sub cmdEmployeeByFielddir_Click()
Dim stDocName As String
stDocName = "rptReport"
DoCmd.OpenReport "rptReport", acPreview, "", "[qry WCC
Report wo Unmatched]![fielddir]=[Forms]![frmMenu]!
[cmbFielddir]"
End Sub

The form field, cmbFielddir, is displayed as a pull down
menu. I use the following code (behind a form button) to
create an Excel spreadsheet (unfiltered). Is there a way
to use the cmbFielddir field to filter output to the
spreadsheet?

Private Sub cmdExcel_Click()
DoCmd.OutputTo acQuery, "WCC Output Report WO
Unmatched", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.ShowToolbar "Print Preview", acToolbarNo
End Sub


-----Original Message-----
The filtering will be done in the query. For example if
you only wanted a specific date range in the criteria
under date in your query you would enter between [Enter
Date 1] and [Enter Date2]. When you run the query you
will be promted for Date1 and Date 2.

Jim
-----Original Message-----
I'm fairly new at this. So I'm still not sure in VB code
how to filter the table.
.
Click to expand...
.
Click to expand...
 
I guess I do not understand your filter. Could you
explain what is the criteria for a record to be exported
to excel?

Jim
-----Original Message-----
What I would like to do is filter from a field in the form
I am using. The following code is behind a button on the
form, frmMenu, which produces a filtered report:

Private Sub cmdEmployeeByFielddir_Click()
Dim stDocName As String
stDocName = "rptReport"
DoCmd.OpenReport "rptReport", acPreview, "", "[qry WCC
Report wo Unmatched]![fielddir]=[Forms]![frmMenu]!
[cmbFielddir]"
End Sub

The form field, cmbFielddir, is displayed as a pull down
menu. I use the following code (behind a form button) to
create an Excel spreadsheet (unfiltered). Is there a way
to use the cmbFielddir field to filter output to the
spreadsheet?

Private Sub cmdExcel_Click()
DoCmd.OutputTo acQuery, "WCC Output Report WO
Unmatched", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.ShowToolbar "Print Preview", acToolbarNo
End Sub


-----Original Message-----
The filtering will be done in the query. For example if
you only wanted a specific date range in the criteria
under date in your query you would enter between [Enter
Date 1] and [Enter Date2]. When you run the query you
will be promted for Date1 and Date 2.

Jim
-----Original Message-----
I'm fairly new at this. So I'm still not sure in VB code
how to filter the table.
-----Original Message-----
Create a macro that runs the query that creates a
temporary table and use the transferspreadsheet option to
export the temporary table to Excel

Jim

-----Original Message-----
I would like to create an Excel spreadsheet using a
query
and filter from the output of a drop down menu (fed by
one
of the database fields) on the form. I can do this with
the OpenReport command, but have not found a way to do
this using the TransferSpreadsheet function. Neither do
I
know another way to export results to a spreadsheet
other
than the TransferSpreadsheet function.

Is there a way I can filter output that is exported to
an
Excel spreadsheet? Also, can I designate the tabs names?
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I guess I haven't done a good job of explaining what I
want to do.

On a form I have a pull down list or menu. Let's say that
the field, which is pull down list, is named combo1.
Let's also say that combo1 offers 4 choices: A, B, C, and
D. Also on the same form I have a button that creates an
Excel spreadsheet from a query (or table)by either using
TransferSpreadsheet or OutputTo. What I want the button
to do is produce a spreadsheet only for the value of
combo1. For example if I choose 'A' from the pull down
combo box and click the button, I want to get a
spreadsheet where a specified field in my query (or table)
is equal to combo1's value and produces a spreadsheet for
all records matching that criteria.

Neither the TransferSpreadsheet method nor the OutputTo
method has a feature for filtering. I tried using If Then
unsuccessfully. If you have an example (or examples) of
any VB code that would allow me to perform this simple
task, I would much appreciate it.
-----Original Message-----
I guess I do not understand your filter. Could you
explain what is the criteria for a record to be exported
to excel?

Jim
-----Original Message-----
What I would like to do is filter from a field in the form
I am using. The following code is behind a button on the
form, frmMenu, which produces a filtered report:

Private Sub cmdEmployeeByFielddir_Click()
Dim stDocName As String
stDocName = "rptReport"
DoCmd.OpenReport "rptReport", acPreview, "", "[qry WCC
Report wo Unmatched]![fielddir]=[Forms]![frmMenu]!
[cmbFielddir]"
End Sub

The form field, cmbFielddir, is displayed as a pull down
menu. I use the following code (behind a form button) to
create an Excel spreadsheet (unfiltered). Is there a way
to use the cmbFielddir field to filter output to the
spreadsheet?

Private Sub cmdExcel_Click()
DoCmd.OutputTo acQuery, "WCC Output Report WO
Unmatched", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.ShowToolbar "Print Preview", acToolbarNo
End Sub


-----Original Message-----
The filtering will be done in the query. For example if
you only wanted a specific date range in the criteria
under date in your query you would enter between [Enter
Date 1] and [Enter Date2]. When you run the query you
will be promted for Date1 and Date 2.

Jim

-----Original Message-----
I'm fairly new at this. So I'm still not sure in VB code
how to filter the table.
-----Original Message-----
Create a macro that runs the query that creates a
temporary table and use the transferspreadsheet option
to
export the temporary table to Excel

Jim

-----Original Message-----
I would like to create an Excel spreadsheet using a
query
and filter from the output of a drop down menu (fed by
one
of the database fields) on the form. I can do this
with
the OpenReport command, but have not found a way to do
this using the TransferSpreadsheet function. Neither
do
I
know another way to export results to a spreadsheet
other
than the TransferSpreadsheet function.

Is there a way I can filter output that is exported to
an
Excel spreadsheet? Also, can I designate the tabs
names?
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top