Find and remove filters from an Excel file

[nagiese]

I have a macro in Access that exports data to an Excel file. Other users of the Excel file have been putting filters on the data tab which is causing my macro to bug out. How do I have the macro open the file, look for filters and then remove any existing filters on the Data sheet?

My current VBA is as follows (I’m trying to teach myself so please excuse the code I’m sure there is more efficient ways to write this):

Option Compare Database

Option Explicit

Public Sub ExpMonthEndFGFMISSmryLotDat()

'Step 1: Declare your Variables
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As Parameter
    Dim MyConnect As String
    Dim MyRange As Range
    Dim MyRange2 As Range
    Dim c As Range
    Dim d As Range
             
    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.Application")
    Dim xlwkbk As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Dim i As Integer
    Dim Cols_to_Insert As Single
    
'Step 2:Declare your connection string
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= U:\Finance\Balance Sheet\Inventory\Finished_Goods.accdb; User ID = Admin;"
    
    DoCmd.Hourglass True
Set db = CurrentDb
Set qdf = db.QueryDefs("slq-FMIS143_FG_Smry_Brand_Lot")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

'Step 1: Instantiate Excel. If Excel isn't Loaded, Error # 429 occurs.
    Set xl = GetObject(, "Excel.Application")
   
'Step 2: Open STAT34 Detailed Master.xlsm
    Set xlwkbk = xl.Workbooks.Open("U:\Finance\Balance Sheet\Inventory\2015\FMIS-FINC_ME_Reporting.xlsx")
    Set xlsheet = xlwkbk.Worksheets("FMIS-FG&WIP_by_Lot")
    xl.Visible = True
    xlwkbk.Windows(1).Visible = True
        xlwkbk.Worksheets("FMIS-FG&WIP_by_Lot").Activate
        xl.Range("A2:XFD1048576").ClearContents

'Step 3: Paste Data into Excel File
    With xlsheet
    xl.Range("A2").CopyFromRecordset rst
    End With
    
'Step 4: save as text file & Close active recorset
    xl.Visible = True
    xlwkbk.Save
    xlwkbk.Close (True)
    xl.WindowState = xlMinimized
    MsgBox "FMIS143 FG by Lot Detail Data Has Been Exported to Excel"
    
'Step 5: Memory Clean up
    Set xlsheet = Nothing
    Set xlwkbk = Nothing
    Set xl = Nothing
    Set db = Nothing
    rst.Close
    
    DoCmd.Hourglass False
    Forms![frm-FINC/FMIS_ME_Reporting]![lblDone2].Visible = True
    
End Sub

[June7]

Bing: Excel VBA remove filters

http://stackoverflow.com/questions/2...-filters-macro