Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

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

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now