CODEWITHSUNDEEP
excelvba
Jackery Xu
Jackery Xu

Reputation: 402

automation error with adding filter

I'm trying to add an AutoFilter to a sheet in an excel file:

'---- SLIDE THREE ----
Sub SlideTangerine3() 'reportWeek As String)

'Declare variables
Dim pre As Presentation
Dim slide As slide
Dim textbox As Shape
Dim book As Workbook
Dim sheet As Worksheet
Dim switch As String

'Set variables
Set pre = ActivePresentation
Set slide = pre.Slides(3)

'-----------------------------------------------------

Set book = Workbooks.Open("C:\Users\User\Desktop\Book1.xlsx")
Set sheet = book.Worksheets(3)

If Not sheet.AutoFilterMode Then
    sheet.Range("A1").AutoFilter
End If

book.Close

End Sub

I click run, vba editor freezes

After ending process, I get message "automation error the remote procedure call failed"

Upvotes: 0

Views: 474

Answers (1)

David Zemens
David Zemens

Reputation: 53623

From comments above, I do notice that you're doing book.close without book.save. This will raise a dialog asking you to save or discard changes to the workbook. Applying the AutoFilter -- even without any conditions -- is a "change".

Because Excel Application is not visible, you don't see this dialog, and it is stuck in an infinite waiting for you to respond (but you can't respond, because you can't see it!). Either of these methods I think should resolve your problem.

Option 1 Make the workbook visible. This will allow the dialog to display, and then you can choose to save or discard changes. 

Sub SlideTangerine3() 'reportWeek As String)

'Declare variables
Dim pre As Presentation
Dim slide As slide
Dim textbox As Shape
Dim book As Workbook
Dim sheet As Worksheet
Dim switch As String

'Set variables
Set pre = ActivePresentation
Set slide = pre.Slides(3)

'-----------------------------------------------------

Set book = Workbooks.Open("C:\Users\User\Desktop\Book1.xlsx")
book.Application.Visible = True
Set sheet = book.Worksheets(3)

If Not sheet.AutoFilterMode Then
    sheet.Range("A1").AutoFilter
End If

book.Close

End Sub

Option 2 explicitly save (or don't save) the file, this will avoid the dialog box altogether:

Sub SlideTangerine3() 'reportWeek As String)

'Declare variables
Dim pre As Presentation
Dim slide As slide
Dim textbox As Shape
Dim book As Workbook
Dim sheet As Worksheet
Dim switch As String

'Set variables
Set pre = ActivePresentation
Set slide = pre.Slides(3)

'-----------------------------------------------------

Set book = Workbooks.Open("C:\Users\User\Desktop\Book1.xlsx")
Set sheet = book.Worksheets(3)

If Not sheet.AutoFilterMode Then
    sheet.Range("A1").AutoFilter
End If

book.Close True 
'### OR to discard changes, use this:
'book.Close False

End Sub

Upvotes: 1

Related Questions