Creating excel from VB Crystal.

Posted on 2005-03-10
Medium Priority
Last Modified: 2008-02-01
   I have two challenges here.

   Currently I am generating crystal report (CR8) in my VB6.  No problem.
   Next I would like to save it as Excel sheet.  Again no problem I manged to do this.
   However, when I open this excel sheet  it display error:
Problem 1)-----v
   Microsoft Excel File Repair Log

   Errors were detected in file 'C:\DOCUME~1\alad\LOCALS~1\Temp\C.Notes.Data\expiring_policy_list_GU.xls'
   The following is a list of repairs:

   Renamed invalid sheet name.  

Problem 1)-----^
Here's my code snippet:------v
               Set oCRReport = Nothing
                Set oCRReport = oCRApp.OpenReport(App.Path & "\" & c_sCRYSTALREPORT_RENEWAL)
                Set oCRDB = oCRReport.Database
                Set oCRTbls = oCRDB.Tables
                Set oCRTbl = oCRTbls.Item(1)
                Call oCRTbl.SetLogOnInfo(m_sServer, Right(m_sServer, 4), m_sUsername, m_sPassword)
                Set oCRFFs = oCRReport.FormulaFields
                Set oCRFF = oCRFFs(1)
                oCRReport.RecordSelectionFormula = "{V_AUTOREN.COMPANYID} = '" & pCompanyID & "' and " & _
                                        "{V_AUTOREN.BRANCHID} = '" & pBranchID & "'"
                oCRReport.ProgressDialogEnabled = False

                sExportFilename = App.Path & "\expiring_policy_list_" & pCompanyID & pBranchID & ".xls"    '// excel filename
                Call Kill(sExportFilename)
                On Error GoTo EH
                Set oExportOptions = oCRReport.ExportOptions
                oExportOptions.DestinationType = crEDTDiskFile
                oExportOptions.DiskFileName = sExportFilename
                oExportOptions.FormatType = crEFTExcel50
                Call oCRReport.Export(False)
Here's my code snippet:------^
            Is there anything I have to add in the VB code to prevent the error each time I open the VB created
Excel file?

Problem 2----v
     Once I create the excel sheet,  then
      i) how can I set protection on this excel file so that user cannot copy/edit but only see it?
     ii) same as i) plus user is able to filter the data, ie sort columns?

    Basically I want users to look at my data and filter it the way they like but don't want them to alter it?
    Do I even need to create excel, can I do it another format?  May be access file, may be Crystal report file? pdf file?
    Again the key thing is whatever file my VB program generates, the user should be able to filter and view only
     not copy or edit it.

    Thank you advance for your thoughts on this matter
Problem 2----^

Question by:ALad2005
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
LVL 101

Accepted Solution

mlmcc earned 1500 total points
ID: 13506511
>>Problem 2
Since it creates a temporary file on their machine and the FILE --> SAVE AS is available I cannot think of anyway to protect the file that the user cannot get around.

Why not just present them with the report in a report viewer.  You can use an application to provide filtering and sorting options.

Here are the updated export drivers.  They may solve your other problem



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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

771 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