Export to Excel - Locking a column

bhareth
bhareth Member Posts: 55
edited 2011-01-05 in General Chat
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

Comments

  • Belias
    Belias Member Posts: 2,998
    I don't think it's possible with the standard export function. why do you need to lock the column?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • bbrown
    bbrown Member Posts: 3,268
    Range.Locked
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    XLRange is 'Microsoft Excel Object Library'.Range
    Lock is Boolean TRUE
            XlRange := XlWrkSht.Range(xlColID + xlRowID);  
        XlRange.Locked(Lock);
    There are no bugs - only undocumented features.
  • bhareth
    bhareth Member Posts: 55
    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
    Belias Member Posts: 2,998
    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?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • bhareth
    bhareth Member Posts: 55
    Sorry to inform , I am using 2009 classic client to export the report to excel (calling CreateSheet function on excel buffer table).
  • Belias
    Belias Member Posts: 2,998
    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?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • bhareth
    bhareth Member Posts: 55
    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
    Belias Member Posts: 2,998
    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...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • bbrown
    bbrown Member Posts: 3,268
    You also need to protect the worksheet:
    XlWrkSht._Protect
    There are no bugs - only undocumented features.
  • bhareth
    bhareth Member Posts: 55
    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
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.

Categories