0

Is there a way to prepare a virtual object in Word using vba? I am trying to speed up adding about 20000+ Document Variables into the body of a document using the Fields.Add method. 

Selection.Fields.Add Range:=Selection.Range, _
                     Type:=wdFieldEmpty, _
                     Text:="DOCVARIABLE " & DocVar.Name & " ", _
                     PreserveFormatting:=True

I'm thinking that adding them to some sort of "virtual paragraph" instead, and appending the document with this "virtual paragraph" right at the end might speed things up.

Additional explanation and testing: (for those that love reading)

Adding 1000 DOCVARIABLE fields to the end of my document takes about 14 seconds with Application.Visible = False. This time seems to go up exponentially the more variables I try to add. I haven't been able to add 20000 Variables yet. With Application.Visibility off, I don't know if the program has crashed or not but 20mins seems too short. This isn't even getting into adding the variables into tables and applying the sorting algorithms I want to apply after that. I would also like the Application.Visibility to be on so that I can display progress of processing in the status bar. But visibilty more than tripples my processing time.

1000 DocVars = 14s
2000 DocVars = 35s
3000 DocVars = 70s
4000 DocVars = 101s
...
20000 will be unusably slow

Thanks

Lance

braX
  • 11,506
  • 5
  • 20
  • 33
LanceDango
  • 23
  • 1
  • 7
  • 2
    If you could tell us you are trying to put 20,000+ Document variables in a Word document, someone may be able to suggest a more rational approach to your problem. – freeflow Apr 13 '20 at 09:28
  • 1
    Also: 1) working with Selection objects is way less efficient than working with Range objects; and 2) you probably need to give Word some breathing space via DoEvents periodically (e.g. every 500 insertions). – macropod Apr 13 '20 at 09:40
  • @freeflow Thanks. Any ideas would be welcome. I have a complicated document register spreadsheet from which I want to export the most critical data for each document to an independent Word Document as a report. The template of this report document has pre-existing fields from which I wish my code to discern where to place additional fields. In the report, I want to create macros for users to further filter and sort the report information. I wish Word DocVariables included array types but alas I'm stuck with sending 2153 rows of 12 data points (2153 x 12 = 25836) as individual strings. – LanceDango Apr 13 '20 at 10:18
  • @macropod Thank you. Looks like having your macros make repeated selections is quite time consuming. Once I removed ['.Paragraphs.Last.Range.Select] ['.Content.InsertParagraphAfter] statements in every iteration things speeded up quite a bit. Even so, the code is intolerably slow for my purposes. So my original question about populating some offscreen object still stands. – LanceDango Apr 13 '20 at 10:30
  • Hmm, reports in Word - can't you give your users Excel reports? Thousands of values and filtering and so on is much more natural to Excel, you can incluse some pretty graphs either :) – Arvo Apr 13 '20 at 12:21
  • «Word DocVariables included array types but alas I'm stuck with sending 2153 rows of 12 data points (2153 x 12 = 25836) as individual strings» That still doesn't justify the use of DOCVARIABLES or adding so many DOCVARIABLE fields in code.Since you evidently know where in the document each DOCVARIABLE field is to be written, why are you not writing the Excel values directly to the same locations? Way faster. In any case, I see no evidence you've explored periodically giving Word some breathing space via DoEvents. – macropod Apr 13 '20 at 12:50
  • 1
    If you *really* need to insert large numbers of DOCVARIABLE fields, you can build a Word 2003 XML format document in VBA that contains as many as you want, very quickly and insert it using Range.InsertXML but the insertion/update) can cause Word problems, possibly repaginating, Here, 20000 take forever, whereas 20 blocks of 1000 with DoEvents is much faster. But you mentionarray types. Are you trying to insert blocks of data from the Excel sheet as tables? Perhaps worth looking at Range.InsertDatabase to help you do some that. –  Apr 13 '20 at 13:22
  • @slightlysnarky Yes. I'm a bit shaky on the use of XML at the moment but I'm realising more and more that it's very useful. Going to study up on this a bit as I will need it on some other projects as well. – LanceDango Apr 14 '20 at 08:12
  • @macropod I'm realising the limitations of DocVariable fields too. Was hoping to keep them because their usefulness for additional functionality I want to build in. I was hoping that the report template could inform the macro where to place the data instead of the other way around so the third parties could edit templates to their preference. For now it looks like placing direct data will have to do. I have added calls to DoEvents every 10 lines and it has at least given me the opportunity to access Word on the taskbar while the macro is running. – LanceDango Apr 14 '20 at 08:21
  • @Arvo Yeah, we've been using Excel so far but for various reasons I want to migrate the Form and Report functionality to word with the central database still remaining our Excel spreadsheets. Word is really good at pagination and managing headers, footers and document layout. As an Excel fanatic it took a long while to come to the decision to give Word a try. Apart from a few drawbacks (which will get fixes or workarounds), I am very impressed with the results so far. – LanceDango Apr 14 '20 at 09:29
  • Why not bookmarks instead of doc variable fields if you need to access the data again at a later point? – Cindy Meister Apr 15 '20 at 07:50

0 Answers0