Solved

Crystal Reports and VB Export to template

Posted on 2014-04-09
7
751 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 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 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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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