Export data from another query

[Guest]

Hi every1

I got a form based on a general query. i want to use a button to export data
from another query, but i want to limit the dates. *i.e. from 01-aug-04 to
30-aug-04.
Any ideas?
(my knoledge of VB is NONE)

i tried to export using the following but it just opened the excel.. and
nothing else

Private Sub SBTLog_Click()
On Error GoTo Err_SBTLog_Click
Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
DoCmd.TransferSpreadsheet acExport, 8, QSBTLog, "C:\my
documents\SBTLog.xls", False
Exit_SBTLog_Click:
Exit Sub
Err_SBTLog_Click:
MsgBox Err.Description
Resume Exit_SBTLog_Click
End Sub

Thanks

 

[Ken Snell [MVP]]

No need to open EXCEL in order to use TransferSpreadsheet. If the query that
you want to export either already has that filter on it, or the query is
reading a textbox from your form to get a filtering parameter, just do the
TransferSpreadsheet action all by itself.

 

[Guest]

Hi Ken.
The querry does not have the filter, i want to make it flexible in order to
analyze the data....

 

[Ken Snell [MVP]]

Where are you filtering a query in the code that you posted? I'm not seeing
that unless the query QSBTLog already contains a parameter as I noted
earlier.

 

[Guest]

I have not added the filter yet. The code that i attached is just for trying
to export the whole query to excel.

 

[Ken Snell [MVP]]

If you want to use TransferSpreadsheet to export a filtered query, the
easier ways to do it are to "hardcode" the filter into the stored query
("hardcode" meaning that you give it the actual value for the criterion
value), or to use a control on the form that is running the code to provide
the value needed by the query's criterion expression. In these cases, as I
noted earlier, you only need one line of code to do the export (not the long
code that you posted):

DoCmd.TransferSpreadsheet acExport, 8, QSBTLog, "C:\my
documents\SBTLog.xls", False

If you want to create the filter dynamically in code, it gets more
complicated, as you must create a new query whose SQL statement includes the
dynamically created WHERE clause, save that query, export that query, and
then delete that query.

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Hi Ken.
I can not make i work...
what i am doing is add that command. this button is inserted in a form that
is bases n a query (QGeneral). i made another query (QSBTLog) selecting some
fields from Qgeneral to make some calculations. and this last one is the one
i want to export to excel from the form (that is based on QGeneral).

I hope u can help me.

thanks

 

[John Nurick]

Hi Mike,

As Ken said, to export the query all you need is something like

DoCmd.TransferSpreadsheet acExport, 8, QSBTLog, _
"C:\Documents and Settings\UserName\My Documents\SBTLog.xls", _
False

(Note that on most computers C:\My Documents is not a valid folder, and
the My Documents folder is under C:\Documents and Settings.)

To confine the query to a particular range of dates, the simplest way is
to make it a parameter query. Help on this is under
Create a parameter query
in English-language versions of Access.

It is possible to make a parameter query automatically take values (e.g.
dates) from a form. This is explained in detail at
http://office.microsoft.com/en-gb/assistance/HA011170771033.aspx