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
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.
I hope this helps.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments