exporting to excel

[border20]

Exporting ! HELP PLZ

How do i export a field of a table to an excel doc ??

or even better ! can I export a SQL statement ?

 

[spacepro]

I'm not an expert but you can export the whole table then just delete the columns that are unwanted or create a query with the field you require, then create a report based on the query.

When you have created the report you can sort it into ascending/descending order, then run the report and then right click and select export.....OR create a macro.

There are several options but the macro might be the way to go.

Andy

 

[border20]

ow woulk ok ,,, thx, i'll look into that,,, but i'm not sure it will be usable for what i need to do ...

I have a table of pruducts... and I want the user to be able to select search criterias (in a form) and see the normal distribution in a excell sheet... but only the distribution of part of the table...

so the best thing would be to export a SQL statement

 

[Jerry Stoner]

The code below will do what you want

Dim objExcel As Object
Dim strFile As String

strFile = "C:\qcdatabase\chartbuffer.xls"


DoCmd.OutputTo acOutputQuery, "qryScrapChart", acFormatXLS, strFile, False
Set objExcel = GetObject(strFile)
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True
objExcel.Windows(1).WindowState = xlMinimized


ExitHere:
Exit Sub

 

[Pat Hartman]

TransferSpreadsheet can export a table or a query. In fact, just about any place that you can use a table, you can substitute a query name.

 

[border20]

ok ok

ok thx but there is something i want to do before i export the table...

I have a result table (3 results fields and other descriptive fields)with numerus reccords.

I only need to export those 3 relult fields for a limited number of reccords... depending on the descriptive fields...

What i was thinking of doing, is creating a buffer table... before the export, i would empty the buffer table and repopulate it with certain records...« filtered by an SQL statement...

is this a good idea ?

if so .... how to I empty a table ??
and how do I copy certian records from a table to another ?? something like select ... then copy ???


thx !

 

[Jerry Stoner]

You want a make table query. The table will be overwritten each time you run the query.

 

[Pat Hartman]

There is no need to make a temporary table. Just reference the query in the TransferSpreadsheet rather than the table.

 

[border20]

Pat Hartman

I'm not sure i understand.... I'm still a n00b in access...

what do you mean by

Just reference the query in the TransferSpreadsheet rather than the table. ?

One other thing I thought... can the criterias of a query be changed with code ?

 

[border20]

Another thing a thought about... i could use a parameter query... but the parameters would have to come from a list in a form...

how would i do this ??

and would it export the right data ?

 

[Pat Hartman]

Create a query that references form fields to obtain parameters. Use that query name in the TransferSpreadsheet Method. As long as the form is open when the code runs, it will be fine.