0

I've got an Excel VBA script to reformat and sort data I import from a CSV file. The script was developed with Excel 2013, and it used to work fine. I updated Office 2013 to 2019, and the script fails with runteim error 91 "Object variable or With-Block variable not set".

enter image description here

The part of the code which fails is:

    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A2:A10001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Add Key:= _
        Range("D2:D10001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E2:E10001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The error occurs at line ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Clear

I tried to find out more by recording a macro. The steps recorded are:

  • Click at cell A1
  • Activar filters
  • Open sort dialogue and add two columns as sort keys, close dialog
  • End recording

This is what Excel recorded

Sub Makro1()
'
' Makro1 Makro
'

'
    Range("TimeData5[[#Headers],[Start]]").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Tabelle1").ListObjects("TimeData5").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Tabelle1").ListObjects("TimeData5").Sort.SortFields. _
        Add2 Key:=Range("TimeData5[Start]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Tabelle1").ListObjects("TimeData5").Sort.SortFields. _
        Add2 Key:=Range("TimeData5[Dauer]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Tabelle1").ListObjects("TimeData5").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

The main difference between my code and the recorded one is .ListObjects("TimeData5") in all the sort operations.

The object name (TimeData5) seems to be the the file name of the CSV file the data was imported from, amended by a numeric suffix. The problems with this:

  1. The name of the CSV file changes.
  2. The suffix is set, and incremented, by Excel in some known mannor.

Why is ListObjects() needed in Excel 2019, but it was not in Excel 2013? Is there a workaround to write the VBS so that it is not dependent on the CSV file name?

braX
  • 11,506
  • 5
  • 20
  • 33
phunsoft
  • 2,674
  • 1
  • 11
  • 22
  • maybe try to replace `.ListObjects("TimeData5")` with `.ListObjects(1)` (should refer to first list object no matter what its name is) and see if it works? – garbb Jun 03 '23 at 20:32
  • @garbb Thanks, but no. Excel complains with "runtime error 9: Index outside valid range" (Index ausserhalb des gültigen Bereichs. – phunsoft Jun 03 '23 at 21:01

1 Answers1

1

There are two things at play here.

One is that somehow tables are created inside that worksheet (.ListObjects hints at that), which may be in connection with the version change, however, other parts of the code should be seen to tell it for sure.

The other is that .Autofilter may have changed between 2013 and 2019 (I don't have all versions of Excel installed), which causes that Worksheet.Autofilter does not work anymore.

If you don't like the tables: Fix the other part of the code that creates them, then insert before each .Autofilter a reference, .UsedRange, like this:

ActiveWorkbook.Worksheets("Tabelle1").UsedRange.AutoFilter.Sort.SortFields.Clear
z32a7ul
  • 3,695
  • 3
  • 21
  • 45
  • The rest of the code is not important; it's only moving, renaming, moving columns, and some cell formtting. I have recreted a minimal failing subroutine before posting. Sorry, I missed the `Sub ...()` and `End Sub` statements, but have just added them in the Q. The code *does not* insert the data from the CSV; I'm doing this part manually before running the script. And, yes, the dialog to import the data looks very different to the on in 2013. I'll see if there are options on how the data is inserted. Thanks. – phunsoft Jun 04 '23 at 13:01
  • You can remove table objects via the UI: click in the table, then Ribbon > Table Tools > Design > Convert to Range, or via VBA: `ActiveWorkbook.Worksheets("Tabelle1").ListObjects("TimeData5").Unlist`. – z32a7ul Jun 04 '23 at 18:35