Link to home
Start Free TrialLog in
Avatar of Anil Lad
Anil LadFlag for Canada

asked on

Creating excel from VB Crystal.

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

ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial