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
Solved

Crystal Reports and VB Export to template

Posted on 2014-04-09
7
733 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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 …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

807 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