A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Export multiple reports to same Excel workbook



 
 
Thread Tools Display Modes
  #1  
Old November 23rd, 2005, 04:43 AM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default Export multiple reports to same Excel workbook

Hi

Can someone point me in the right direction for doing the above?

I have 3 reports, I would like to save all 3 into the same Excel workbook.
Each on its own worksheet [tab]. Im happy with the current report
formatting/grouping etc (understand I will loose some formatting, thats OK).

Thanks
AC


  #2  
Old November 24th, 2005, 04:46 PM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default Export multiple reports to same Excel workbook

AC:

Below is one alternative for accomplishing such a task. Because the
OutputTo method overwrites an existing workbook, some additional work is
required to get the report worksheets into one workbook. The following
function creates a different Excel workbook for each report and then copies
the worksheets into one workbook. The code does not delete the individual
report workbooks after copying, so you will need to add this functionality
if required. Of course, you will need a reference to the appropriate Excel
Object Library.

This also answers you second post regarding password protecting the
workbook. The third parameter of the Workbook SaveAs method allows you to
specify a password for the workbook (it is case-sensitive).

Function ExportReports()
Dim rpt As AccessObject
Dim xl As New Excel.Application
Dim wkbMerged As Excel.Workbook
Dim wkbSource As Excel.Workbook
Dim sWorkbookName As String
Dim iNumOfWorksheets As Integer

'Get the default setting in Tools/Options for the number of worksheets
in a new workbook
iNumOfWorksheets = xl.SheetsInNewWorkbook

'Temporarily reset this value to one
xl.SheetsInNewWorkbook = 1

'Open a new workbook to hold the exported Excel reports
Set wkbMerged = xl.Workbooks.Add

'Loop through the reports and select those only those with "employee" in
the name
For Each rpt In CurrentProject.AllReports
If InStr(1, rpt.Name, "Employee", vbTextCompare) 0 Then
'Build an Excel export filename based on the name of the report
sWorkbookName = "C:\Test" & rpt.Name & ".xls"

'Do the export
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatXLS,
sWorkbookName

'Open the previously exported report and copy into our "Merge"
workbook, then close it
Set wkbSource = xl.Workbooks.Open(sWorkbookName)
wkbSource.Worksheets.Copy wkbMerged.Worksheets("Sheet1")
wkbSource.Close
End If
Next rpt

'Delete the extra empty worksheet, Sheet1
wkbMerged.Worksheets("Sheet1").Delete

'Reset our default value
xl.SheetsInNewWorkbook = iNumOfWorksheets

'Save our "Merged" workbook with a password and close
wkbMerged.SaveAs "C:\TestMergedReports.xls", xlWorkbookNormal,
"Password"
wkbMerged.Close
xl.Quit

Set rpt = Nothing
Set wkbMerged = Nothing
Set wkbSource = Nothing
Set xl = Nothing

End Function


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"A C" wrote in message
...
Hi

Can someone point me in the right direction for doing the above?

I have 3 reports, I would like to save all 3 into the same Excel workbook.
Each on its own worksheet [tab]. Im happy with the current report
formatting/grouping etc (understand I will loose some formatting, thats OK).

Thanks
AC



  #3  
Old November 25th, 2005, 12:20 AM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default Export multiple reports to same Excel workbook

Thankyou Mr Lloyd, works like a charm

On a related tangent, to avoid needing to play around with references I
defined xl as Object, and then did:
Set xl = CreateObject("Excel.Application")

Similar minor changes for the workbook objects.

Is there any disadvantage for what I have done? I did it to prevent needing
to muck around with references at the site where this is going to be put.

Regards
AC

"David Lloyd" wrote in message
.. .
AC:

Below is one alternative for accomplishing such a task. Because the
OutputTo method overwrites an existing workbook, some additional work is
required to get the report worksheets into one workbook. The following
function creates a different Excel workbook for each report and then

copies
the worksheets into one workbook. The code does not delete the individual
report workbooks after copying, so you will need to add this functionality
if required. Of course, you will need a reference to the appropriate

Excel
Object Library.

This also answers you second post regarding password protecting the
workbook. The third parameter of the Workbook SaveAs method allows you to
specify a password for the workbook (it is case-sensitive).

Function ExportReports()
Dim rpt As AccessObject
Dim xl As New Excel.Application
Dim wkbMerged As Excel.Workbook
Dim wkbSource As Excel.Workbook
Dim sWorkbookName As String
Dim iNumOfWorksheets As Integer

'Get the default setting in Tools/Options for the number of worksheets
in a new workbook
iNumOfWorksheets = xl.SheetsInNewWorkbook

'Temporarily reset this value to one
xl.SheetsInNewWorkbook = 1

'Open a new workbook to hold the exported Excel reports
Set wkbMerged = xl.Workbooks.Add

'Loop through the reports and select those only those with "employee"

in
the name
For Each rpt In CurrentProject.AllReports
If InStr(1, rpt.Name, "Employee", vbTextCompare) 0 Then
'Build an Excel export filename based on the name of the

report
sWorkbookName = "C:\Test" & rpt.Name & ".xls"

'Do the export
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatXLS,
sWorkbookName

'Open the previously exported report and copy into our "Merge"
workbook, then close it
Set wkbSource = xl.Workbooks.Open(sWorkbookName)
wkbSource.Worksheets.Copy wkbMerged.Worksheets("Sheet1")
wkbSource.Close
End If
Next rpt

'Delete the extra empty worksheet, Sheet1
wkbMerged.Worksheets("Sheet1").Delete

'Reset our default value
xl.SheetsInNewWorkbook = iNumOfWorksheets

'Save our "Merged" workbook with a password and close
wkbMerged.SaveAs "C:\TestMergedReports.xls", xlWorkbookNormal,
"Password"
wkbMerged.Close
xl.Quit

Set rpt = Nothing
Set wkbMerged = Nothing
Set wkbSource = Nothing
Set xl = Nothing

End Function


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or

warranties.


"A C" wrote in message
...
Hi

Can someone point me in the right direction for doing the above?

I have 3 reports, I would like to save all 3 into the same Excel workbook.
Each on its own worksheet [tab]. Im happy with the current report
formatting/grouping etc (understand I will loose some formatting, thats

OK).

Thanks
AC





  #4  
Old November 25th, 2005, 03:32 PM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default Export multiple reports to same Excel workbook

AC:

Whether you use early binding or late binding (what you have chosen to
implement) depends on the exact circumstances of the application you are
developing. The following KB article gives more details regarding the
advantages and disadvantages of using one versus the other.

http://support.microsoft.com/default...b;en-us;245115

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"A C" wrote in message
...
Thankyou Mr Lloyd, works like a charm

On a related tangent, to avoid needing to play around with references I
defined xl as Object, and then did:
Set xl = CreateObject("Excel.Application")

Similar minor changes for the workbook objects.

Is there any disadvantage for what I have done? I did it to prevent needing
to muck around with references at the site where this is going to be put.

Regards
AC

"David Lloyd" wrote in message
.. .
AC:

Below is one alternative for accomplishing such a task. Because the
OutputTo method overwrites an existing workbook, some additional work is
required to get the report worksheets into one workbook. The following
function creates a different Excel workbook for each report and then

copies
the worksheets into one workbook. The code does not delete the individual
report workbooks after copying, so you will need to add this functionality
if required. Of course, you will need a reference to the appropriate

Excel
Object Library.

This also answers you second post regarding password protecting the
workbook. The third parameter of the Workbook SaveAs method allows you to
specify a password for the workbook (it is case-sensitive).

Function ExportReports()
Dim rpt As AccessObject
Dim xl As New Excel.Application
Dim wkbMerged As Excel.Workbook
Dim wkbSource As Excel.Workbook
Dim sWorkbookName As String
Dim iNumOfWorksheets As Integer

'Get the default setting in Tools/Options for the number of worksheets
in a new workbook
iNumOfWorksheets = xl.SheetsInNewWorkbook

'Temporarily reset this value to one
xl.SheetsInNewWorkbook = 1

'Open a new workbook to hold the exported Excel reports
Set wkbMerged = xl.Workbooks.Add

'Loop through the reports and select those only those with "employee"

in
the name
For Each rpt In CurrentProject.AllReports
If InStr(1, rpt.Name, "Employee", vbTextCompare) 0 Then
'Build an Excel export filename based on the name of the

report
sWorkbookName = "C:\Test" & rpt.Name & ".xls"

'Do the export
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatXLS,
sWorkbookName

'Open the previously exported report and copy into our "Merge"
workbook, then close it
Set wkbSource = xl.Workbooks.Open(sWorkbookName)
wkbSource.Worksheets.Copy wkbMerged.Worksheets("Sheet1")
wkbSource.Close
End If
Next rpt

'Delete the extra empty worksheet, Sheet1
wkbMerged.Worksheets("Sheet1").Delete

'Reset our default value
xl.SheetsInNewWorkbook = iNumOfWorksheets

'Save our "Merged" workbook with a password and close
wkbMerged.SaveAs "C:\TestMergedReports.xls", xlWorkbookNormal,
"Password"
wkbMerged.Close
xl.Quit

Set rpt = Nothing
Set wkbMerged = Nothing
Set wkbSource = Nothing
Set xl = Nothing

End Function


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or

warranties.


"A C" wrote in message
...
Hi

Can someone point me in the right direction for doing the above?

I have 3 reports, I would like to save all 3 into the same Excel workbook.
Each on its own worksheet [tab]. Im happy with the current report
formatting/grouping etc (understand I will loose some formatting, thats

OK).

Thanks
AC






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
suggestion for excel tool option David Coleman General Discussions 7 November 10th, 2005 04:33 AM
AHHHH-Get Data from Multiple Excel workbooks JAA149 General Discussion 5 October 30th, 2005 06:19 PM
How do I merge multiple quicken reports into one workbook? TheBusMomma General Discussion 1 October 5th, 2005 12:02 PM
Crystal Reports Export to Excel - Retaining Leading Zeros CR-Tech General Discussion 1 November 1st, 2004 07:15 PM
cannot open workbook outside of excel jnc General Discussion 4 July 14th, 2004 11:01 AM


All times are GMT +1. The time now is 09:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.