?
Solved

Crystal Reports and VB Export to template

Posted on 2014-04-09
7
Medium Priority
?
765 Views
Last Modified: 2014-04-09
I have CR with VS 2010 (CR ver. 13.0.2)
I export to excel just fine using the following code Below.

Is there any way in VB to export to an existing Excel template and specifying the worksheet to export it to?
Right now, I manually copy the data over to the template and refresh the Pivot tables in the template. I would like to cut out this step.
Much appreciated.
Thx

Code:

Dim exportOpts As New ExportOptions()
        Dim diskOpts As New DiskFileDestinationOptions()
        Dim excelFormatOpts As New ExcelFormatOptions()
        exportOpts = report.ExportOptions

        excelFormatOpts.ExcelTabHasColumnHeadings = True
        excelFormatOpts.ShowGridLines = True
        exportOpts.ExportFormatType = ExportFormatType.Excel
        exportOpts.ExportFormatType = ExportFormatType.Excel
        exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
        diskOpts.DiskFileName = "C:\Temp\JobListing " & intSecond & ".xls"
        exportOpts.DestinationOptions = diskOpts
        report.Export()
       
        Dim newXL As Excel.Application
        Dim newWB As Excel.Workbook

        Dim excelFileName As String = diskOpts.DiskFileName
        newXL = New Microsoft.Office.Interop.Excel.Application
        newXL.Visible = True
        newWB = newXL.Workbooks.Open(excelFileName)
0
Comment
Question by:BKennedy2008
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 39988863
Have you tried recording the steps you use manually then converting the macro to code?

mlmcc
0
 

Author Comment

by:BKennedy2008
ID: 39988869
I manually copy from the excel worksheet that crystal reports exports and paste it into the template. A macro will not work in this case.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39988932
When you copy are you just copying the entire spreadsheet into the template or are you selecting single cells or small groups of cells and pasting them?

Does cell A1 go to cell A1 in the template?
Similarly for all cells in the spreadsheet?

mlmcc
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

Author Comment

by:BKennedy2008
ID: 39989015
I select all cells that column A contains Data, I can copy the sheet since no formulas are on that sheet.
Let's say other Network users will do the same for this report.
I have:
Report A.xlsx : A report that is generated at runtime from Crystal reports
Report B.xlsx : A report that is considered a template that I have pivot tables for.

Are you suggesting:
Have Report A generated to the network drive folder (U:\Reports\ in this case)
Have Report B Reside in the network folder.
Create a Macro on Report B that performs a copy from Report A, sheet A to Report B Sheet A.

I think this would work great. I will give it a shot. I didn't think about that. I was too hung up on trying to export to an existing file from Crystal.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39989147
That is one way to do it.

I was thinking about adding the code to the application so that when Crystal was done with the export, the code would continue and do the copy to a new workbook with the appropriate template applied.
The Crystal exported version could then be archived if or deleted.

mlmcc
0
 

Author Comment

by:BKennedy2008
ID: 39989166
I think I am good. I tweeked it out with VB button to open the template, and then it runs the macro at workbook.open and then the macro opens up the network CR excel file and copies it over, then refreshes the pivot tables.
It is about as easy as it gets.
That will work. Thanks for leading me in that direction!
0
 

Author Comment

by:BKennedy2008
ID: 39989459
To complete it I did the following:
-Created the Macro on the template
-With a push of a  button in a windows app, I export the crystal report to a network excel file
- Opened the Excel app from VB, made it invisible
- Ran the Macro from VB that copied the sheets and refreshed the pivot tables
- Saved and closed the report from VB
- Sent the report to the executives through system.net.mail

So no user intervention occurs when they push the button, except a successful/ Failed message.

Thanks alot
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question