1 Reply Latest reply: Sep 25, 2012 5:01 AM by Gerhard Laubscher RSS

    When Bookmarked Selections doesn't exist

    Gerhard Laubscher

      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
          Gerhard Laubscher

          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?