Set Analysis in visual Basic Macro (select a field)

[#1]

Hi all,

(thanks in advande for any suggests)

I'm trying to select a field using a visual basic macro with set analysis or only max function, but it doesn't work:

the code:

[...]

Set LBy = ActiveDocument.GetSheetObject("LB09")

Set LBydate= ActiveDocument.GetSheetObject("LB08") //in this object there are a list of MY_DATE

    valy = LBy.GetPossibleValues

    valyDate = LBydate.GetPossibleValues

    for y=lbound(valy) to ubound(valy)

                vSelectedItemy = valy(y)

                Set Fieldy = LBy.GetField

                Set FieldyDate = LBydate.GetField

                Time_BeforeSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

                   Fieldy.Select vSelectedItemy

                    ActiveDocument.GetApplication.WaitForIdle

                   FieldyDate.Select("max({<LOCATION = 'vSelectedItemy'>}date(MY_DATE))")

                   'fine aggiunta igor

                Time_AfterSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

                ActiveDocument.GetApplication.WaitForIdle

In row :

FieldyDate.Select("max({<LOCATION = 'vSelectedItemy'>}date(MY_DATE))")

This expression doesn't return any error, but no MY_DATE is selected... also this doesn't work(more simple):

FieldyDate.Select("max(MY_DATE)")

but max(MY_DATE) and max({<LOCATION = 'SameLocation'>} date(MY_DATE)) work if I put it in a button as action :S ... How is it possible?

Thanks for any suggest!

[#2 · Accepted solution]

I think you're trying to make this more complicated than it probably is.

The statement below is your first problem:

FieldyDate.Select("max({<LOCATION = 'vSelectedItemy'>}date(MY_DATE))")

You're trying to make two selections and once for some reason.  The set analysis is just another selection, so why not just select it separately?

Look at the example below:

'set up a variable called vMaxDate

'=max(MY_DATE)

set x= ActiveDocument.GetField("MY_DATE")

set y = ActiveDocument.GetField("LOCATION")

y.Select vSelectedItemy

maxDate = ActiveDocument.Variables("vMaxDate").GetContent.String

x.Select maxDate

'first it selects vSelectredItemy (which is what you have it doing in the set analysis).  Once it does that,

'the variable changes to whatever the max date is, so you can just select maxDate

Also I didn't know you could GetPossibleValues from a list box but that doesn't mean you can't.

Hopefully some of that made sense

[#3]

Trent Jones, thank you very much, your reply is very useful for me!  : )

(Even if: in the case of need i cannot  use set analysis into macro )

FYI (and for everyone in the future had my problem) this is the macro that i use for iterate into a field (location), select a max for an other one (max POST_DATE for each location) and finally export a single table with different selection in an unique excel file, it works :

sub PrintDetails

' Set Excel App

        set XLApp = CreateObject("Excel.Application") ' Define Object

        XLApp.Visible = True 'Visible set as true

        set XLDoc = XLApp.Workbooks.Add 'Open new workbook   

          'XLDoc.Worksheets("Sheet1").Delete

          'XLDoc.Worksheets("Sheet2").Delete

    ' Get TB1

Set LBy = ActiveDocument.GetSheetObject("LB09") 'must be a List Box objext, it contains my list of Location

Set LByDate = ActiveDocument.GetSheetObject("LB08") 'must be a List Box objext, it contains my list of post_date

Set FieldyDate = LByDate.GetField

    valy = LBy.GetPossibleValues

    for y=lbound(valy) to ubound(valy)

                vSelectedItemy = valy(y)

                Set Fieldy = LBy.GetField

                Time_BeforeSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

                    Fieldy.Select vSelectedItemy

                    ActiveDocument.GetApplication.WaitForIdle   

                    FieldyDate.Select ActiveDocument.Evaluate("max(POST_DATE)")

                Time_AfterSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

                ActiveDocument.GetApplication.WaitForIdle   

                'Riga commentata mi serviva per debug dell'espressione

                'MsgBox(ActiveDocument.Evaluate("max(POST_DATE)"))

                set table = ActiveDocument.GetSheetObject("CH05")

        ' Copy table and paste into Excel

    set XLSheet = XLDoc.Worksheets.Add 'Select sheet where data should be pasted

        table.CopyTableToClipboard true 'Copy data to Clipboard

        XLSheet.Paste XLSheet.Range("A1") 'Paste data into cell

        XLSheet.Name= valy(y)

            next 'Year

            Fieldy.Clear

            'XLDoc.Worksheets("Sheet3").Delete

end sub