[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Crystal Reports and VB Export to template

Posted on 2014-04-09
7
Medium Priority
?
783 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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