Hi there,
I ran into a potential nightmare, and I hope there is a way out:
I've been spending hours an a macro (already 5,000 lines long), which generates a report based on selecting bookmarks, then sending all the possible Account Numbers to excel, then selecting a random one and deleting the rest. Once I am done it will repeat this process for about 300 bookmarks (the macro might run for 30 minutes, but it will save us 3 to 4 hours work).
The problem is that certain bookmarks might have zero accounts. The data changes daily, so today bookmarkX might match 1 or 2 accounts, but tomorrow none.
When I select a bookmark that matches NO accounts, it will still activate the bookmark but it will make zero selections in the field where it cannot find a possible valaue.
What I want the macro to do is only select a bookmark if it exists and matches 100%, otherwise it must skip it and go on with the next bookmark.
I feel this is impossible - but PLEASE tell me it can be done.
Here is what my macro looks like (it pretty much just repeats itself for all bookmarks and changes the range in Excel; I've included 2 bookmarks here):
'<<<<<<Dormant Account - NO STATEMENT Product 5 (start)>>>>>>>>>
ActiveDocument.RecallDocBookmark "Dormant5"
XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"
set LB = ActiveDocument.GetSheetObject("LB28")
boxvalues=LB.GetPossibleValues
for i = lbound(boxvalues) to ubound(boxvalues)
XLSheet1.cells(i+1,1).Value = boxvalues(i)
next
XLSheet1.Range("B2").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"
XLApp.Worksheets(1).Range("B3") = XLApp.Worksheets(1).Range("B2")
XLApp.Worksheets(1).Range("B2") = XLApp.Worksheets(1).Range("B3")
XLApp.Worksheets(1).Range("B3").Delete
XLApp.Worksheets(1).Range("B1") = "5. ASA"
XLApp.Worksheets(1).Range("A:A").Delete
XLApp.Worksheets(1).Range("A:A").Insert
'<<<<<<Dormant Account - NO STATEMENT Product 5 (end)>>>>>>>>>
'<<<<<<Dormant Account - NO STATEMENT Product 6 (start)>>>>>>>>>
ActiveDocument.RecallDocBookmark "Dormant6"
XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"
set LB = ActiveDocument.GetSheetObject("LB28")
boxvalues=LB.GetPossibleValues
for i = lbound(boxvalues) to ubound(boxvalues)
XLSheet1.cells(i+1,1).Value = boxvalues(i)
next
XLSheet1.Range("C2").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"
XLApp.Worksheets(1).Range("C3") = XLApp.Worksheets(1).Range("C2")
XLApp.Worksheets(1).Range("C2") = XLApp.Worksheets(1).Range("C3")
XLApp.Worksheets(1).Range("C3").Delete
XLApp.Worksheets(1).Range("C1") = "6. HTSA"
XLApp.Worksheets(1).Range("A:A").Delete
XLApp.Worksheets(1).Range("A:A").Insert
'<<<<<<Dormant Account - NO STATEMENT Product 6 (end)>>>>>>>>>
Please let me know if you need more detail or if it doesn't make sense...
Thanks,
Gerhard
