Enable AutoFilter on Excel protected sheet using OLE2_OBJECT

[prince_isaac]

Hi guys

I have created my excel sheet using OLE2_OBJECT and managed to protect the sheet and format columns as required however now i need to have AutoFilter on two columns on the sheet.  I am only managing to have one column at a time showing the AutoFilter and i can only have this AutoFilter usable/enabled if the password protection is off.  I have done some research and discovered that using VBA coding this is possible as detailed in this link: http://office.microsoft.com/en-za/excel-help/enable-autofilter-functionality-for-a-protected-workshe...

I have done my coding for the column formating like this:

call method of lw_excel 'Columns' = lw_columns
       exporting
         #1 = 9.
     set property of lw_columns 'AutoFilter' = 1.
call method of lw_excel 'Columns' = lw_columns
       exporting
         #1 = 10.
     set property of lw_columns 'AutoFilter' = 1.

This however is not working on column 10.  How can i enable the AutoFilter on both column 9 and 10 and still have the sheet password protected.

regards

PrinceIsaac

[prince_isaac]

Hi guys

I have created my excel sheet using OLE2_OBJECT and managed to protect the sheet and format columns as required however now i need to have AutoFilter on two columns on the sheet.  I am only managing to have one column at a time showing the AutoFilter and i can only have this AutoFilter usable/enabled if the password protection is off.  I have done some research and discovered that using VBA coding this is possible as detailed in this link: http://office.microsoft.com/en-za/excel-help/enable-autofilter-functionality-for-a-protected-workshe...

I have done my coding for the column formating like this:

call method of lw_excel 'Columns' = lw_columns
       exporting
         #1 = 9.
     set property of lw_columns 'AutoFilter' = 1.
call method of lw_excel 'Columns' = lw_columns
       exporting
         #1 = 10.
     set property of lw_columns 'AutoFilter' = 1.

This however is not working on column 10.  How can i enable the AutoFilter on both column 9 and 10 and still have the sheet password protected.

regards

PrinceIsaac

[prince_isaac]

Hi guys

I accidentaly clicked Answered.  I am no way near solving the issue as of now.

regards

Prince Isaac

[Former Member]

Hi Prince,

Can you Provide your Complete Code .

Regard's

Smruti

[Former Member]

Can you check this Methods/ Properties.....

Methods
	Name	Description
	ApplyFilter	Applies the specified Autofilter object.
	ShowAllData	Displays all the data returned by the AutoFilter object.

Properties
	Name	Description
Application	When used without an object qualifier, this property returns an Application object that represents t...
Creator	Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.
FilterMode	Returns True if the worksheet is in the AutoFilter filter mode. Read-only Boolean.
Filters	Returns a Filters collection that represents all the filters in an autofiltered range. Read-only.
Parent	Returns the parent object for the specified object. Read-only.
Range	Returns a Range object that represents the range to which the specified AutoFilter applies.
Sort	Gets the sort column or columns, and sort order for the AutoFilter collection.

Regard's

Smruti

[prince_isaac]

Hi Smruti

I am unable to get two columns on my sheet to have the AutoFilter set.  The code i sent above correctly sets the AutoFilter on the 9th column but not on the 10th.  The properties/methods you mentioned are exactly what i am trying to understand how i can use to achieve my goal.

regards

Prince Isaac

[Former Member]

Hi Prince ,

Check this Sample code for Auto filter set for 9th and 10th Column .

CALL METHOD OF H_EXCEL 'Cells' = GS_CELL1

     EXPORTING

     #1 = 1                                   " 1st Row

    #2 = 9.                                   " 9 th Column

   CALL METHOD OF H_EXCEL 'Cells' = GS_CELL2

     EXPORTING

     #1 = 1                                  "  1st Row

     #2 = 10.                               " 10th Column

   CALL METHOD OF H_EXCEL 'Range' = GS_CELLS

     EXPORTING

     #1 = GS_CELL1

     #2 = GS_CELL2.

   CALL METHOD OF GS_CELLS 'Select' .

SET PROPERTY OF GS_CELLS 'AutoFilter' = 1.

Regard's

Smruti

[prince_isaac]

Hi Smruti

I tried the above suggestion and it did not work for me.  It actually distorted the output file by moving the data 9 columns to the right.  I tried changing around the coding but no luck.

regards

[Former Member]

Can you Share your Complete Code .

with some sample input and output what actually you want .

Regard's

Smruti