Creating excel from VB Crystal.

   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----^

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

>>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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.