Export a reprot to Excel

Hi,
I have created a report (using Design mode) that gets values from various
queries. For each report it shows kind of Course Title at the top. I have
tried to export it to Excel or even RTF and the exporting completes but there
is nothing inside these files when I open them.

Any ideas?

Hi,
open the report as a report and then try to export it to excel. Unless you
are using A2007, that would normally work.
I haven't moved up to A2007 yet, so am not in a position to advise on that
version.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Hi,
I have created a report (using Design mode) that gets values from various
queries. For each report it shows kind of Course Title at the top. I have
tried to export it to Excel or even RTF and the exporting completes but
there
is nothing inside these files when I open them.

Any ideas?

I am actually openning it as a report. Moreover, I am using Access 2003 not
2007.
I think I have some other reports that are generated through wizards and
they were exported correctly. This is the only report I have done using
Design since Access 2003 was showing me a message (when I tried to do it
through wizard) that Access is unable to generate the report, please select
only one query or only one table.

Note: My report is based on several independent queries (each of them is
summing something and I am showing them in one report).


"Jeanette Cunningham" wrote:

Hi,
open the report as a report and then try to export it to excel. Unless you
are using A2007, that would normally work.
I haven't moved up to A2007 yet, so am not in a position to advise on that
version.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Hi,
I have created a report (using Design mode) that gets values from various
queries. For each report it shows kind of Course Title at the top. I have
tried to export it to Excel or even RTF and the exporting completes but
there
is nothing inside these files when I open them.

Any ideas?

Thanks for the extra info.
The export process will try to export the recordsource of the report.
Do you have a single query as the recordsource of this report - your
description suggests that maybe you don't.
If you can create a single query based on the independent queries, you will
have a much better chance of exporting the report.
Your post says independent queries, is there any way you could combine
them - perhaps a union query?


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
I am actually openning it as a report. Moreover, I am using Access 2003 not
2007.
I think I have some other reports that are generated through wizards and
they were exported correctly. This is the only report I have done using
Design since Access 2003 was showing me a message (when I tried to do it
through wizard) that Access is unable to generate the report, please
select
only one query or only one table.

Note: My report is based on several independent queries (each of them is
summing something and I am showing them in one report).


"Jeanette Cunningham" wrote:

Hi,
open the report as a report and then try to export it to excel. Unless
you
are using A2007, that would normally work.
I haven't moved up to A2007 yet, so am not in a position to advise on
that
version.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Hi,
I have created a report (using Design mode) that gets values from
various
queries. For each report it shows kind of Course Title at the top. I
have
tried to export it to Excel or even RTF and the exporting completes but
there
is nothing inside these files when I open them.

Any ideas?

No, Access did not allow me to combine the queries since this would be very
complex. I need to find a way to export without having a combined query.

Regards


"Jeanette Cunningham" wrote:

Thanks for the extra info.
The export process will try to export the recordsource of the report.
Do you have a single query as the recordsource of this report - your
description suggests that maybe you don't.
If you can create a single query based on the independent queries, you will
have a much better chance of exporting the report.
Your post says independent queries, is there any way you could combine
them - perhaps a union query?


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
I am actually openning it as a report. Moreover, I am using Access 2003 not
2007.
I think I have some other reports that are generated through wizards and
they were exported correctly. This is the only report I have done using
Design since Access 2003 was showing me a message (when I tried to do it
through wizard) that Access is unable to generate the report, please
select
only one query or only one table.

Note: My report is based on several independent queries (each of them is
summing something and I am showing them in one report).


"Jeanette Cunningham" wrote:

Hi,
open the report as a report and then try to export it to excel. Unless
you
are using A2007, that would normally work.
I haven't moved up to A2007 yet, so am not in a position to advise on
that
version.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Hi,
I have created a report (using Design mode) that gets values from
various
queries. For each report it shows kind of Course Title at the top. I
have
tried to export it to Excel or even RTF and the exporting completes but
there
is nothing inside these files when I open them.

Any ideas?

Public,
you could export each query separately, using DoCmd.TransferSpreadsheet.
However to export the results of each query on to the same spreadsheet
requires some vba code to automate excel.
It is doable, but you need to be comfortable with coding using vba.

Alternatively, you could set up a template worksheet.
It would have a separate worksheet for each query you need to export.
It would have a page that gets the final full report by linking to the data
from each worksheet.

This could simplify the coding quite a bit.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
No, Access did not allow me to combine the queries since this would be
very
complex. I need to find a way to export without having a combined query.

Regards


"Jeanette Cunningham" wrote:

Thanks for the extra info.
The export process will try to export the recordsource of the report.
Do you have a single query as the recordsource of this report - your
description suggests that maybe you don't.
If you can create a single query based on the independent queries, you
will
have a much better chance of exporting the report.
Your post says independent queries, is there any way you could combine
them - perhaps a union query?


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
I am actually openning it as a report. Moreover, I am using Access 2003
not
2007.
I think I have some other reports that are generated through wizards
and
they were exported correctly. This is the only report I have done using
Design since Access 2003 was showing me a message (when I tried to do
it
through wizard) that Access is unable to generate the report, please
select
only one query or only one table.

Note: My report is based on several independent queries (each of them
is
summing something and I am showing them in one report).


"Jeanette Cunningham" wrote:

Hi,
open the report as a report and then try to export it to excel. Unless
you
are using A2007, that would normally work.
I haven't moved up to A2007 yet, so am not in a position to advise on
that
version.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Hi,
I have created a report (using Design mode) that gets values from
various
queries. For each report it shows kind of Course Title at the top. I
have
tried to export it to Excel or even RTF and the exporting completes
but
there
is nothing inside these files when I open them.

Any ideas?

Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards


"Jeanette Cunningham" wrote:

Public,
you could export each query separately, using DoCmd.TransferSpreadsheet.
However to export the results of each query on to the same spreadsheet
requires some vba code to automate excel.
It is doable, but you need to be comfortable with coding using vba.

Alternatively, you could set up a template worksheet.
It would have a separate worksheet for each query you need to export.
It would have a page that gets the final full report by linking to the data
from each worksheet.

This could simplify the coding quite a bit.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
No, Access did not allow me to combine the queries since this would be
very
complex. I need to find a way to export without having a combined query.

Regards


"Jeanette Cunningham" wrote:

Thanks for the extra info.
The export process will try to export the recordsource of the report.
Do you have a single query as the recordsource of this report - your
description suggests that maybe you don't.
If you can create a single query based on the independent queries, you
will
have a much better chance of exporting the report.
Your post says independent queries, is there any way you could combine
them - perhaps a union query?


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
I am actually openning it as a report. Moreover, I am using Access 2003
not
2007.
I think I have some other reports that are generated through wizards
and
they were exported correctly. This is the only report I have done using
Design since Access 2003 was showing me a message (when I tried to do
it
through wizard) that Access is unable to generate the report, please
select
only one query or only one table.

Note: My report is based on several independent queries (each of them
is
summing something and I am showing them in one report).


"Jeanette Cunningham" wrote:

Hi,
open the report as a report and then try to export it to excel. Unless
you
are using A2007, that would normally work.
I haven't moved up to A2007 yet, so am not in a position to advise on
that
version.


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Hi,
I have created a report (using Design mode) that gets values from
various
queries. For each report it shows kind of Course Title at the top. I
have
tried to export it to Excel or even RTF and the exporting completes
but
there
is nothing inside these files when I open them.

Any ideas?

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards

Thanks for your response.
However, when I tried the code, I faced the following compilation error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO 3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards