select multiple field values with macro

[riho92qv]

Hi

I need a macro that allows you to dynamically select different values for the same field.
I have a field YearWeek that contains data in the format yyyy-ww,  for example 2020-40, 2020-4, 2020-42 ...
I would like to write a macro that would first select the current week, then export the table to Excel. Then select the last 12 weeks and export the same table to the same excel file. Sending to excel spreadsheet is not a problem, but the problem is to select weeks dynamically (weeks are determined using formulas or variables) using a macro.

I currently use two buttons that make weekly selections with selectinfield and run a macro. I would like to combine these activities into one macro.

Riho

[riho92qv]

Hi
I was looking for more solutions and working macros are Test2 and test3. Unfortunately, they are very 'awkward'.
Macro test4 does not work. Maybe someone can suggest a more elegant solution

sub test2
With ActiveDocument.Fields("YearMonth")
    .Select ActiveDocument.Evaluate("Date(Date#(AddMonths(Today(),0), 'DD.MM.YYYY'), 'YYYY-MM')")
    .ToggleSelect ActiveDocument.Evaluate("Date(Date#(AddMonths(Today(),-1), 'DD.MM.YYYY'), 'YYYY-MM')")
    .ToggleSelect ActiveDocument.Evaluate("Date(Date#(AddMonths(Today(),-2), 'DD.MM.YYYY'), 'YYYY-MM')")
    .ToggleSelect ActiveDocument.Evaluate("Date(Date#(AddMonths(Today(),-3), 'DD.MM.YYYY'), 'YYYY-MM')")
    ''''''  etc.
End With
end sub

'---------------------------------------------------------------------------------------------------------------------------------
sub test3
With ActiveDocument.Fields("YearWeek")
    .Select ActiveDocument.Evaluate("Year(Today() - 0 * 7 ) & '-' & Week(Today() - 0 * 7)")
    .ToggleSelect ActiveDocument.Evaluate("Year(Today() - 1 * 7 ) & '-' & Week(Today() - 1 * 7)")
     .ToggleSelect ActiveDocument.Evaluate("Year(Today() - 2 * 7 ) & '-' & Week(Today() - 2 * 7)")
    .ToggleSelect ActiveDocument.Evaluate("Year(Today() - 3 * 7 ) & '-' & Week(Today() - 3 * 7)")
     ' . . . etc.
End With
end sub

'----------------------------------------------------------------------------------------------------------------------------------
This macro does not work properly, cycle n = 1 to 4 does not work

sub test4
With ActiveDocument.Fields("YearWeek")
    .Select ActiveDocument.Evaluate("Year(Today() - 0 * 7 ) & '-' & Week(Today() - 0 * 7)")
    For n = 1 to 4
        .ToggleSelect ActiveDocument.Evaluate("Year(Today() - n * 7 ) & '-' & Week(Today() - n * 7)")
    Next
End With
end sub

[marcus_sommer]

" ... does not work ..." isn't really helpful, what happens - it breaks in any way, any error-message is shown, nothing happens, it runs but doesn't select the right values ...?

Just from the view it looked ok. whereby I'm not sure if nesting a for-loop within a with-statement is valid/supported within VBS. Quite often are msgbox-statements very useful to find issues within the code, means something like:

msgbox n

or

msgbox err.number & " - " & err.description

Beside this I suggest to skip this part and to create appropriate continuous fieldvalues within the datamodel in the master-calendar. This may be done with autonumber(Year & '|' & Month) or with calculations like Year * 12 + Month.

With it you could use a logic like:

... fields("YearMonthXXX").select ">=YourValue"

Because of the fact that all your selections are related to today() you may even create appropriate flag-fields within the master-calendar so that you could simply use:

... fields("YearMonthFlag").select 1

- Marcus

[riho92qv]

Hi Marcus
thanks for the advice. I tried to test a bit using msgbox.

sub test4
    With ActiveDocument.Fields("YearWeek")
        .Select ActiveDocument.Evaluate("Year(Today(0) - 0 * 7 ) & '-' & Week(Today(0) - 0 * 7)")
        For n = 1 to 4
                 msgbox a(n)
                msgbox (ActiveDocument.Evaluate("Year(Today(0) - n * 7 ) & '-' & Week(Today(0) - n * 7)"))
       .ToggleSelect ActiveDocument.Evaluate("Year(Today(0) - n * 7 ) & '-' & Week(Today(0) - n * 7)")
   Next
End With
end sub

First msgbox returned numbers 1 to 4, but the second one returned only '-'.
I tested another expression:
msgbox ActiveDocument.Evaluate("Year(Today(0) - 0 * 7 ) & '-' & Week(Today(0) - 0 * 7)")
it is ok, I conclude that the use of the variable n in the loop does not work.

Riho

[riho92qv]

Hi

The last option is what happens:

" ... does not work ..." isn't really helpful, what happens - it breaks in any way, any error-message is shown, nothing happens, it runs but doesn't select the right values ...?

Riho

[marcus_sommer · Accepted solution]

Now I see the issue - you used evaluate() which transfers your statement within the UI and there the macro-variable n is unknown respectively hasn't a proper value.

To remain by your logic you could try it with an additionally step like:

ActiveDocument.Variables("n").SetContent n

Another method would be to create the right value directly within the macro, maybe with:

year(date - n * 7) & "-" & format(...)

How to get a proper week-value take a google-search AFAIK you need format() for it.

- Marcus