Export Possible Values

gerhardl — Tue, 18 Sep 2012 06:47:36 GMT

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

Re: Export Possible Values

m_woolf — Tue, 18 Sep 2012 11:02:32 GMT

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 — Tue, 18 Sep 2012 11:19:15 GMT

Works perfectly - thanks!