export form results to an excel file?

[FriPi]

Hi
I have the following issue. I have a subform where data from a query is
shown following the selection made on my main form.
The data shown on my subform has been rearranged to have a certain layout,
different from the one in the source query.

I would like to export the data shown in my subform to an excel file with
exactly the same layout as in access.
This is the code I use, but the problem is the exported excel file has the
layout of the source query.
Is there any way to export data from a subform with exactly the same
layout???

Dim oExcel As Excel.Application
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim I As Long
On Error Resume Next
Err.Clear
Set oExcel = GetObject(, "Excel.Application ")
If Err.Number <> 0 Then Set oExcel = CreateObject("Excel.Application")
On Error GoTo 0

With oExcel
.Visible = True
.workbooks.Add
.ActiveSheet.Name = CStr("SheetName")
Set DB = CurrentDb
Set RS = DB.OpenRecordset("Query", dbOpenSnapshot)
For I = 0 To RS.Fields.Count - 1
.Cells(1, I + 1) = RS.Fields(I).Name
Next I
.Range("A2").Select
.Selection.CopyFromRecordset RS

 

[BrerGoose]

Why not loop through the fields on your form, instead of the recordset names ?
BrerGoose

 

[Gary Walter]

Hi FriPi,

Are you asking...

you have a query "Query" with some fields, say

f1 f2 f3 f4 f5

but in subform you display them like

f2 f4 f1 f5 f3

and you would like them to end up in
a new worksheet (say "WrkSht1")
in a new xls file (say "C:\test.xls")
ordered as

f2 f4 f1 f5 f3

then I believe the following query
is all you need:

SELECT
f2, f4, f1, f5, f3
INTO
[Excel 8.0;database=C:\test.xls].WrkSht1
FROM Query;

good luck,

gary

 

[Gary Walter]

I wish I had said...

and you would like them to end up in
a new worksheet (say "WrkSht1")
in a *new or existing* xls file (say "C:\test.xls")

i.e., test.xls can be a new xls
or test.xls can already exist
and you are adding new worksheet
to it...all from the simple query...

Hi FriPi,

Are you asking...

you have a query "Query" with some fields, say

f1 f2 f3 f4 f5

but in subform you display them like

f2 f4 f1 f5 f3

and you would like them to end up in
a new worksheet (say "WrkSht1")
in a new xls file (say "C:\test.xls")
ordered as

f2 f4 f1 f5 f3

then I believe the following query
is all you need:

SELECT
f2, f4, f1, f5, f3
INTO
[Excel 8.0;database=C:\test.xls].WrkSht1
FROM Query;

good luck,

gary

I have the following issue. I have a subform where data from a query is
shown following the selection made on my main form.
The data shown on my subform has been rearranged to have a certain
layout,
different from the one in the source query.

I would like to export the data shown in my subform to an excel file with
exactly the same layout as in access.
This is the code I use, but the problem is the exported excel file has
the
layout of the source query.
Is there any way to export data from a subform with exactly the same
layout???

Dim oExcel As Excel.Application
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim I As Long
On Error Resume Next
Err.Clear
Set oExcel = GetObject(, "Excel.Application ")
If Err.Number <> 0 Then Set oExcel = CreateObject("Excel.Application")
On Error GoTo 0

With oExcel
.Visible = True
.workbooks.Add
.ActiveSheet.Name = CStr("SheetName")
Set DB = CurrentDb
Set RS = DB.OpenRecordset("Query", dbOpenSnapshot)
For I = 0 To RS.Fields.Count - 1
.Cells(1, I + 1) = RS.Fields(I).Name
Next I
.Range("A2").Select
.Selection.CopyFromRecordset RS

 

[FriPi]

Hi
how do you loop through the fields on the form? the only function i know is
recordset and it doesn't works because in the form i use the some functions
like =sum[sumOf f1] / [sumOf f2]. in other words i use the sum of the sum
calculated in the query...

is there any method to export a form just like a "print screen" without
touching the query data??

thanks

 

[Alex Dybenko]

Hi,
Dim ctl As Access.Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
'do something
end if
next

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi
how do you loop through the fields on the form? the only function i know
is
recordset and it doesn't works because in the form i use the some
functions
like =sum[sumOf f1] / [sumOf f2]. in other words i use the sum of the sum
calculated in the query...

is there any method to export a form just like a "print screen" without
touching the query data??

thanks

Why not loop through the fields on your form, instead of the recordset
names ?
BrerGoose