Export Possible Values

[gerhardl]

Hi there,

I want to write a macro that creates a report for me.

First thing I am struggling with is to export a list of POSSIBLE values from a list box after I make a selection using a bookmark.

SET XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

SET XLDoc = XLApp.Workbooks.Add

SET XLSheet1 = XLDoc.Worksheets(1)

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\TEST\RandomTest " & strYear & strMonth & strDay &".xlsx"

ActiveDocument.RecallDocBookmark "Dormant6"

XXXXXXXXXXXXXXXXXXXXXXXXXXX            

XLApp.Visible = True

XLDoc.SaveAs NewFileName

' XLApp.Quit

End Sub

Where you see the XXXX's is where I now need to select possible valaues from LB31 and paste it in Cell A1 of the New Excel file I created.

Thanks in advance.

Gerhard

[m_woolf · Accepted solution]

Can you change your listbox or created another for the export.

Then instead of listing a field, enter the expression:

aggr(Field,Field)

Now the listbox displays only possible values.

Then export to excel.

[gerhardl]

Works perfectly - thanks!