Anil Lad
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.No tes.Data\e xpiring_po licy_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_sSer ver, Right(m_sServer, 4), m_sUsername, m_sPassword)
Set oCRFFs = oCRReport.FormulaFields
Set oCRFF = oCRFFs(1)
oCRReport.RecordSelectionF ormula = "{V_AUTOREN.COMPANYID} = '" & pCompanyID & "' and " & _
"{V_AUTOREN.BRANCHID} = '" & pBranchID & "'"
oCRReport.ProgressDialogEn abled = False
sExportFilename = App.Path & "\expiring_policy_list_" & pCompanyID & pBranchID & ".xls" '// excel filename
Call Kill(sExportFilename)
On Error GoTo EH
Set oExportOptions = oCRReport.ExportOptions
oExportOptions.Destination Type = crEDTDiskFile
oExportOptions.DiskFileNam e = 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----^
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
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
Set oCRDB = oCRReport.Database
Set oCRTbls = oCRDB.Tables
Set oCRTbl = oCRTbls.Item(1)
Call oCRTbl.SetLogOnInfo(m_sSer
Set oCRFFs = oCRReport.FormulaFields
Set oCRFF = oCRFFs(1)
oCRReport.RecordSelectionF
"{V_AUTOREN.BRANCHID} = '" & pBranchID & "'"
oCRReport.ProgressDialogEn
sExportFilename = App.Path & "\expiring_policy_list_" & pCompanyID & pBranchID & ".xls" '// excel filename
Call Kill(sExportFilename)
On Error GoTo EH
Set oExportOptions = oCRReport.ExportOptions
oExportOptions.Destination
oExportOptions.DiskFileNam
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.