Solved

Crystal Reports and VB Export to template

Posted on 2014-04-09
7
742 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
  • 4
  • 3
7 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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 100

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 100

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to repeat the data 4 30
locate sql commands in C# visual studio Project 6 45
Copying from a network share 3 26
Adjust the codes 3 37
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…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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