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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
suggestion for excel tool option | David Coleman | General Discussions | 7 | November 10th, 2005 03:33 AM |
AHHHH-Get Data from Multiple Excel workbooks | JAA149 | General Discussion | 5 | October 30th, 2005 05: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 06:15 PM |
cannot open workbook outside of excel | jnc | General Discussion | 4 | July 14th, 2004 11:01 AM |