When Bookmarked Selections doesn't exist

gerhardl — Fri, 21 Sep 2012 08:06:42 GMT

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

Re: When Bookmarked Selections doesn't exist

gerhardl — Tue, 25 Sep 2012 09:01:00 GMT

I now have a macro which creates a chart showing me the number of Accounts that would match each possible bookmark.

it includes this:

ExpressionString = "Count(DISTINCT {'" & bookmarks(i) & "'} [Account no])"

Can I not add something to my Report macro (previous comment), that says if THIS expression is equal to ZERO, then the macro must skip the next X number of lines?

topic When Bookmarked Selections doesn't exist in QlikView

When Bookmarked Selections doesn't exist

Re: When Bookmarked Selections doesn't exist