Protect/Unprotect the excel file cell range using Powerbuilder

[vipinhcl]

Here is the code for protect/unprotect excel cell range using password.

Long li_return,li_row,li_col
String docpath,filename,data
Boolean lb_get

Oleobject ole_excel,ole_sheet

//To get the file path
li_return = GetFileOpenName("Select File",docpath,filename,"XLS","EXCEL Files,*.xls,*.xlsm,*.xlss")

If li_return < 1 then
Messagebox("File","Unable to open the file")
Return -1
End If


ole_excel = CREATE oleObject

li_return = ole_excel.ConnectToNewObject('excel.application')
If li_return < 0 then
Messagebox("Error","Unable to Connect Excel Application")
Return -1
End If


//To off the alert
ole_excel.Application.DisplayAlerts = "False"

ole_sheet = ole_excel.workbooks.open(docpath)

//To hide the file so that it run as backend process
ole_excel.Visible = False

ole_sheet = ole_excel.Application.ActiveWorkbook.WorkSheets[1]

//You can get the number of rows and number of columns from the following commands.
li_row = ole_sheet.UsedRange.Rows.Count
li_col = ole_sheet.UsedRange.Columns.Count

//To select all cells
ole_sheet.Cells.Select
//To unprotect the cells
If ole_sheet.ProtectContents = True Then
ole_sheet.UnProtect("tek-tips") //Using Password
Else
ole_sheet.Unprotect()
End if

//To unlock the cells
ole_sheet.Cells.Locked = False
//To unhide the Formula
ole_sheet.Cells.FormulaHidden = False


//To autofit the columns
ole_sheet.Columns.EntireColumn.AutoFit

//To autofit the rows
ole_sheet.Rows.EntireRow.AutoFit


//Protect the cell
ole_sheet.Range("A1:B2").Select
ole_sheet.Range("A1:B2").Locked = False
ole_sheet.Range("A1:B2").Locked = True

ole_sheet.Protect("tek-tips") //Protect using Password

//Save the Excel file
ole_excel.Application.ActiveWorkbook.save()

//set the workbook status to saved so you can quit without prompting to save
ole_excel.Application.activeworkbook.saved=true

ole_excel.Application.Quit()

// Disconnect from Excel
ole_excel.DisconnectObject()

Messagebox("File Locked","Excel cell range A1:B2 has been locked successfully")

// Destroy the iole Object
DESTROY ole_excel
DESTROY ole_sheet