Search code examples
excelms-accessvbams-office

Last selected value on Excel auto filter dropdown box

I have noticed a strange behavior when I creating a Workbook with auto filter column on Excel XP.

Normally, the last field I had auto filtered is checked on drop down:

Example with expected result

But when I create a workbook via an Access VBA script, I simple do not have this behavior:

Example not working

The code used to generate that workbook is:

Public Function exportToXLS(tableName, controlField)
    [...]

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, queryName, pathWorkbook, True

    [...]
End Function
Solution

  • Digging around and asking to my work colleagues, I find out that to get this behavior the column must be in number-type format.

    Inserting a text to columns in that row to ensure that is in number format I have solved my problem.

    Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True