Autofilter a protected excel sheet opened from access

Question

Im currently moving some code from excel to access. In excel there is a button that opens another excel document and applies an autofilter.

Dim cell As Long
cell = Sheet2.Cells(9, "i").Value    
Workbooks.Open Filename:= _
        "C:/worksheet1.xls"
      Selection.AutoFilter Field:=3, Criteria1:=cell

This is the code from excel and it used to work fine but now also throws an error because the sheet is protected.

Using some code I got from this thread Autofilter Excel with VBA

I came up with code that should work in access but doesn't

What I have so far is

Dim oApp As Object
Dim wb As Object
Dim ws As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

'tries to open workbook
On Error Resume Next
'change file path to the correct one
Set wb = oApp.Workbooks.Open(FileName:="C:/worksheet1.xls")
On Error GoTo 0

'if workbook succesfully opened, continue code
If Not wb Is Nothing Then
    'specify worksheet name
    Set ws = wb.Worksheets("BOM")
    With ws
        'apply new filter
        .Cells(3, 3).Select
        .AutoFilter Field:=3, Criteria1:=110, Operator:=7
    End With
End If

Set wb = Nothing
Set oApp = Nothing

Im getting an error on the .AutoFilter Field:=3, Critera1:=110, Operator:=7 I cant just select a range to autofilter because the sheet is protected and I do not have write access. There is already autofilters in place on the sheet I just need to set the value of one.

Does anybody know a solution to this in either access or excel but preferably both?

Thanks

Kevin Francis Kevin Francis · Accepted Answer · 2014-09-03T16:15:48

I believe an AutoFilter needs to be applied to a Range. Your "With" statement resolves to a worksheet, not a range.

ws.Range(xxxxxx).Autofilter Field:=3, Criteria1:=110, Operator:=7

Unless someone that has access to the worksheet changes the protection using information from the following link, this cannot be done.

http://office.microsoft.com/en-us/excel-help/enable-autofilter-functionality-for-a-protected-worksheet-HA001098270.aspx

I hope this helps.

Autofilter a protected excel sheet opened from access

1 Answers