0

I'm automating a number of reports using a vb.net winforms application. I have one report where I need to update the filter on a Date Field to between 2 dates (Monday & Sunday or the week in question).

I'm getting the dates by having the user select an End Date from a DateTimePicker dateStatusEnd

When the code gets to adding the filter I get the below error message:

enter image description here

The dates are valid because I mistakenly thought I had added them in the wrong order but this gave an error saying the end date had to be less then the start date so it's definitely reading them as errors.

Any ideas how I can resolve this? Code snippet below:

xlSht = aWorkBook.Worksheets("RBA Expiry Dates")

            Dim xlPivot As PivotTable = xlSht.PivotTables("PivotTable1")
            Dim xlPivotField As PivotField = xlPivot.PivotFields("RBA Expiry Date")

            xlPivotField.ClearAllFilters()

            If dateStatusEnd.Value.Day = 31 AndAlso dateStatusEnd.Value.Month = 12 And dateStatusEnd.Value.Date.ToString("dddd") <> "Sunday" Then
                Dim aDate As Date = dateStatusEnd.Value.Date

                Do Until aDate.ToString("dddd") = "Monday"
                    aDate = aDate.AddDays(-1)
                Loop

                xlPivotField.PivotFilters.Add2(Type:=XlPivotFilterType.xlDateBetween, Value1:=aDate.ToShortDateString, Value2:=dateStatusEnd.Value.Date.ToShortDateString)
            Else
                xlPivotField.PivotFilters.Add2(Type:=XlPivotFilterType.xlDateBetween, Value1:=dateStatusEnd.Value.Date.AddDays(-6).Date.ToShortDateString, Value2:=dateStatusEnd.Value.Date.ToShortDateString)
            End If
jbmb2000
  • 61
  • 9
  • Is the Do Until Loop doing something? Cause you have `aDate.ToString("dddd") = "Monday"` instead of `aDate.ToString("dddd").Equals("Monday")`. Same thing with the comparison with "Sunday" on the If statement – Lorenzo Martini Dec 10 '21 at 09:05
  • Sorry Lorenzo, the do loop is subtracting a day until it gets to the next Monday - I'm running a report for a reporting week so Monday - Sunday or Monday - 31st December. The user selects the end date so the loop is just getting the start date. I didn't realise there was a .Equals I'll have to use 5hat in future, thanks (edit - using phone & when trying to go to a new line it saved the comment instead) – jbmb2000 Dec 21 '21 at 01:43

1 Answers1

0

In case anyone else comes across this in the future, it turns out that somewhere between opening the file & applying the filter the Pivot has stop recognising the date column as a date column.

Now I just need to figure out why that's happening

jbmb2000
  • 61
  • 9