EXPORT DATA FROM A FORM TO EXCEL

[bifteki via AccessMonster.com]

I have a subform in a larger form, for which the recordsource is given
through code by a string I make using the user's entry in a text box. I want
to export this data to excel but I don't know how.

To be more specific, we often have to make excel files that contain all
persons that have a specific first name (in Greece we value high our name
days, so we use this for PR, to send our wishes to customers or potential
customers). By pressing a button a pop-up form shows up that contains six
text boxes (in many name days, more than one name is celebrated) were you can
specify up to six strings, either of which must be contained in the person's
first name. When you press another button, "Search", the recordsource of the
main form's subform is set as a string I create in code, so the results show
up (company name, person name & suname etc.).

Is there any way I can export this data to excel?
Maybe I could use docmd.outPutTo? The problem is "docmd.outPutTo" 's use is a
bit limited -as far as I have come to understand- as there are only a few
object types you can export (stored procedure, table etc.)

Olga

 

[bhicks11 via AccessMonster.com]

Yiasou Olga,

Create a query that has it's datasource refer to the form (so for the value
of a particular field put form!form.whatevercontrol as the source), then run:

DoCmd.TransferSpreadsheet - with that query as the table name (either from
VBA or a Macro).

DoCmd.TransferSpreadsheet acExport, 4, _
"YOUR SELECT QUERY NAME","C:\test.xls", True, "A1:G12"

Bonnie
http://www.dataplus-svc.com

 

[bifteki via AccessMonster.com]

Yia sou Bonnie and se efcharisto for your answer

I did it but I get an error message that Access "can't find the object
'sp_export_persons_to_excel".

Maybe I got something wrong.
This is the line I wrote:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"sp_export_persons_to_excel", "W:\DOCS\DRAFTS\eortazontes.xls", True

sp_export_persons_to_excel is a Stored Procedure I created, in which I only
entered the recordsource of the subform with the persons' names as the first
column name.

I also want to add something else: Earlier I managed to get some result. I
had written the following line:
DoCmd.OutputTo acOutputForm, "sbfrm_s_persons_by_surname", acFormatXLS, ,
True

sbfrm_s_persons_by_surname is the subform in which the results are contained.
I got an excel file with the names of the fields as headers, but the data
under each column was just one line, reading "#Name?". Maybe I could somehow
use this method?

Yiasou Olga,

Create a query that has it's datasource refer to the form (so for the value
of a particular field put form!form.whatevercontrol as the source), then run:

DoCmd.TransferSpreadsheet - with that query as the table name (either from
VBA or a Macro).

DoCmd.TransferSpreadsheet acExport, 4, _
"YOUR SELECT QUERY NAME","C:\test.xls", True, "A1:G12"

Bonnie
http://www.dataplus-svc.com

I have a subform in a larger form, for which the recordsource is given
through code by a string I make using the user's entry in a text box. I want

[quoted text clipped - 16 lines]

Olga