Export to Excel - Locking a column

bhareth

Hi ,

After exporting a report to excel i need a column to be locked (i.e user should not be able to edit that column ).

I dont want to do it manually by going to FORMAT CELLS-->LOCK the particular cells and protect the sheet . ](*,)


Thanks,
Bhareth

Belias

I don't think it's possible with the standard export function. why do you need to lock the column?

bbrown

Range.Locked

bbrown

XLRange is 'Microsoft Excel Object Library'.Range
Lock is Boolean TRUE

        XlRange := XlWrkSht.Range(xlColID + xlRowID);  
        XlRange.Locked(Lock);   

bhareth

Belias ,

It is a request from one of our customers .They want users should not be able to edit the amount field on any financial reports after exporting the reports to excel .

bbrown ,

I tried with your Lock code but after exporting the report to excel ,still i can able to edit the particular field which i have made locked .


Thanks,
Bhareth

Belias

I guess they don't want to print the report in pdf format...anyway...
How do you export the report to excel? firstly i thought you were using RTC reports and the standard excel functionality (in that case, i don't think it's possible to implement the column lock); if you don't use the standard function, what code do you use to export the report to excel?

bhareth

Sorry to inform , I am using 2009 classic client to export the report to excel (calling CreateSheet function on excel buffer table).

Belias

ok, then you have to create a function in the excel buffer table that allows you to lock the column, based on the same automation that is used in the excel buffer. where did you put bbrown code?

bhareth

I have created a function LockColumn

Function LockColumn(StartColumn:code;StartRow:Integer;EndColumn:Code;EndRow:Integer;Lock:Boolean);

************code starts
XlRange := XlWrkSht.Range(StartColumn+FORMAT(StartRow) + ':'+EndColumn+FORMAT(EndRow-1));
XlRange.Locked(Lock);
*********** code ends

in the report

ExcelBuf.CreateSheet(...);
LockColumn('A',2,'A',65000,'TRUE');
ExcelBuf.GiveUserControl;


I dont want the column A to be edited by anyone after exporting the report to excel.

correct me if i am wrong anywere!!!!!!! ](*,)

thanks,
bhareth

Belias

try with something like this

XlApp.Range('A1').EntireColumn.Locked(Lock);

if it doesn't work, try to record a macro in excel, do what you want manually and then check the code being generated from excel...

bbrown

You also need to protect the worksheet:

XlWrkSht._Protect

bhareth

Thanks for the reply brown ..
with protect i can able to protect the whole sheet (with a password).once the sheet is protected i am unable to edit other columns apart form the column which is locked .If there is a protect command why i need to lock particular columns??
what i need is that i want to lock particular columns(i.e make that column uneditable) and make other columns editable . ](*,)

Thanks,
bhareth

bbrown

I'm not sure where you are going wrong here. I do this in a situation where I generate a worksheet in which only 1 column remains editable by the user.