Solved

After exporting to Excel the Alignment / Formating is messed up!

Posted on 2004-04-08
4
1,084 Views
Last Modified: 2012-05-04
Hi There,

I am using Crystal Reports 9 and VB 6 for exporting it to Excel. The export works fine but the final exported xls sheet is not what I expect. all the columns have equal width and the allignment is all messed up. I even used ruler lines in the designer to help allign the columns vertically but still no use. But when I export it directly through crystal reports, its perfect. with columns widths and allignment are all according to what i have given in the report.

I am using the following code to export please suggest.

                Dim Appl As New CRAXDRT.Application
                Dim Rpt  As CRAXDRT.Report

                Rpt.ExportOptions.FormatType = crEFTExcel97
                Rpt.ExportOptions.ExcelUseConstantColumnWidth = False  
                Rpt.ExportOptions.DestinationType = crEDTDiskFile
                Rpt.Export False

for the statement "Rpt.ExportOptions.ExcelUseConstantColumnWidth = False" even if I give True here it still gives the same output. I even tried Different FormatTypes like Excel80 etc. But no use.

Thanks,
Faisal.
0
Comment
Question by:faisalee
[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
  • 2
  • 2
4 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 250 total points
ID: 10783042
This is an undocumented "feature" of Crystal.  Crystal attempts to recreate the report in its exact format in Excel.  Unfortunately Excel is probably using different fonts so things end up not aligning as expected.  Also they don't fit into single columns so manipulation is difficult.

Look at
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_20946957.html#10780149

I believe CR9 has the ability to export the data only which might do what you want.  

Because of this problem (I use CR8) and the fact that all my users want a "true" spreadsheet when they export a report I wrote code to do the export using the recordset rather than allow Crystal to do the export.

mlmcc

0
 

Author Comment

by:faisalee
ID: 10803017
Hi mlmcc,

Thanks for the answer... I also thought the same As you said that, "its an undocumented issue in Crystal reports", but still the url to the PDF contains some great info about exporting to Excel :-) Thanks anyways.

Looks like I will have to write code to export directly to excel instead of using crystal reports. The only problem is that we have already finished the 500 + reports to do the this job :-(

Oh well... Maybe we can extend the project a little bit or we can convince the clients to live with the generated XLS files as they are ;-)

Thanks any ways for the prompt reply.

Faisal.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 10803043
I have code already written that uses the query the report is based on to do the export.  I can make it available if you wish.

mlmcc
0
 

Author Comment

by:faisalee
ID: 10803071
Oh great... that will solve half of the problem :-) you can send it to me at <email address removed by mlmcc PE>

Thanks for the great help.

Faisal.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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