List Formatting Does Not Hold on Second Run Through (VBA)


  • Hello all, this is my first forum post here on MSDN so I'm not sure if it's in the right place, my sincerest apologies if it is not.  I also apologize if I went overboard with the amount of information I give, I'm just not sure what is relevant to my problem (if I was sure, I'd probably be able to solve the problem myself).

    I have been creating an Access database application for my firm, but rather than using the built-in report writer I've programmed cross-office functionality to write a report directly to Word.  A lot of my information is dynamic (the report differs based on user selections that are stored in the database), so I was trying to avoid using Word template(s) to accomplish my task.

    My code behaves exactly as I would expect the first time I run it: it opens a new word document (using the existing instance of word, or creating a new one if that doesn't exist) and writes all my text, lists, and tables to the new document with completely proper formatting.  If I run the same routine a second time WITHOUT closing the document that was created the first time I ran the routine everything remains fine and properly formatted.  Opening a new (blank) document while my first run-through's document is still open BEFORE closing my first-run through document and then running my routine again will also result in a perfectly formatted document.

    My issue arises if I close the document which was created by the routine (without opening a new blank document) and then attempt to run the routine again: in the second run-through none of my lists are list-formatted and some of my table formatting is lost.  IE It appears that if I close the instance of word which was used to create the document once, the document will not be properly formatted if I run my routine again.

    I am a fairly competent developer, although Microsoft technologies are relatively new to me, and have worked on this problem for days trying to figure out what is going on here.  For the moment I have focused on trying to get my lists to properly format, since I figure if I can't tackle one problem moving on to the other would probably be useless.

    Some stats about my system/software:

    • I am running Windows 7 with all updates as of April 1, 2012
    • I am running Office 2010 for all my office Applications (including Access)
    • I am using Visual Basic for Applications to implement my cross-office functionality
    • I ALWAYS use Option Explicit (I was initially a C/C++ and Java developer, and dislike the lack of sensitivity in VB, but my firm requires I use the latter)
    • All libraries needed for cross-office functionality are properly selected and loaded (I'm mainly using the Microsoft Word 14.0 Object Library)

    I've been very careful to de-reference ALL my variables which were used to access the database OR MSWord before my routine ends, even in the event of a failure.  I'm pretty sure I've done this in the proper order as well: I close the database connections first, then set my Word.Table variable to nothing, Word.Range variable to nothing, Word.Document variable to nothing, and Word.Application variable to nothing (in that order).

    An example that is similar to my code:

    Public Sub writeReport() Dim appW As Word.Application Dim docW As Word.Document Dim tableW As Word.Table Dim rangeW As Word.Range Dim starting As Long On Error Resume Next Set appW = GetObject(, "Word.Application") If Err.Number <> 0 Then Set appW = CreateObject("Word.Application") End If Set docW = appW.Documents.Add("Normal", False, 0, True) Set rangeW = docW.Range(0, 0) rangeW.SetRange docW.Range.Start, docW.Range.End rangeW.Collapse Word.wdCollapseEnd 'I set the default (plain text) formatting for the whole document here ' ' ' rangeW.SetRange rangeW.End, rangeW.End starting = rangeW.Start 'This is used to define where the list starts rangeW.Collapse Word.wdCollapseEnd rangeW.ListFormat.ListIndent rangeW.Text = "1. Here is a top level list item" & Chr(11) & Chr(11) & _ "Sometimes a single list item has more than one paragraph, so I use chr(11)" _ & Chr(13) 'I set formatting for this range here 'Please assume this happens after every time I set Word.Range.Text 'In fact I use a small subroutine to allow me to quickly select all the formatting 'options I may use, and to write the text to word rangeW.SetRange rangeW.End, rangeW.End rangeW.Text = "2. Here is a second top level list item" & Chr(11) & Chr(13) rangeW.SetRange rangeW.End, rangeW.End rangeW.ListFormat.ListIndent rangeW.Text = "a. Here is a second level list item" & Chr(11) & Chr(13) rangeW.SetRange rangeW.End, rangeW.End rangeW.Text = "b. Here is another second level list item" & Chr(11) & Chr(13) rangeW.SetRange rangeW.End, rangeW.End rangeW.ListFormat.ListIndent rangeW.Text = "i. Here is a third level list item" & Chr(11) & Chr(11) & _ "Sometimes there is more than one paragraph under a single list item" _ & Chr(11) & Chr(13) rangeW.SetRange rangeW.End, rangeW.End rangeW.ListFormat.ListOutdent rangeW.ListFormat.ListOutdent rangeW.Text = "3. Here is a third top level list item" & Chr(11) rangeW.SetRange starting, rangeW.End rangeW.ListFormat.ApplyListTemplate _ ListTemplate:=ListGalleries(Word.WdListGalleryType.wdNumberGallery).ListTemplates(1), _ ContinuePreviousList:=False, ApplyTo:=Word.WdListApplyTo.wdListApplyToWholeList, _ DefaultListBehavior:=Word.wdWord10ListBehavior rangeW.SetRange rangeW.End, rangeW.End rangeW.InsertBreak Type:=wdPageBreak rangeW.SetRange rangeW.End, rangeW.End rangeW.ListFormat.RemoveNumbers NumberType:=Word.wdNumberParagraph rangeW.SetRange rangeW.End, rangeW.End rangeW.Text = "This text might appear after my list in default formatting."

    appW.Visible = True

    Set tableW = Nothing Set rangeW = Nothing Set docW = Nothing Set appW = Nothing End Sub

    I have tried many things to fix my problem, including several variations on the Word.Range.ListFormat.ApplyListTemplate function as well as several variations of the Set docW = Word.Application.Documents.Add function (for the latter I initially had no arguments placed into the function), but nothing I do seems to work.  I have also tried moving the Word.Range.ListFormat.ApplyListTemplate to the beginning of the code which creates my list and setting the ApplyTo argument to Word.WdListApplyTo.wdListApplyToThisPointForward, but it does not seem to solve my problem.

    An additional error that occurs, although it seems unrelated (and does not significantly bother me), is that the last number (not the text) in my list ALWAYS (including on the first run) comes out as the wrong Font, even though all other text and numbers come out as the correct Font.  This doesn't provide any clues to me about what's going on, but maybe someone who understands Microsoft better will see a connection to my actual problem.

    Thanks for your time and help.  Please let me know if you need any additional information.

    Thursday, April 05, 2012 11:19 PM

All replies

  • Hi Citydreaming,

    Thank you for posting.

    Do you still have any problems about your problem? I tested your code snippet, it works well on my side. If you still need any assistant, you can simplify your problem and give us the reproducible step lists, we will be happy to work together with you to resolve the problem.

    Best Regards,

    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, April 10, 2012 2:43 AM
  • Hi Bruce,

    Thanks for your reply.  I am still having the same problem with my code as I explained before.  Here is a step by step:

    1. Open Microsoft Access 2010, select a new blank database.

    2. Close the default table created by a new blank database, do not save.  Open the visual basic editor and create a new module.

    3. Set the new module to option explicit, and load the Microsoft Word 14.0 Object Library reference.

    4. Copy the code (from my original post) and run it once.

    5. Close the Microsoft Word document created by the code, do not save.

    6. Run the code again.

    On the second run through, although all text is still printed (and indented) appropriately, text that should be formatted as a list is formatted as plain text for me (IE the list template is not applied to it), which is my problem.  I am willing to believe that I have something poorly chosen in my Word or Access settings since my firm does not allow me to personally install and maintain the software on my system, although I have spent a little time trying to play around with my settings to no avail.  In contrast, if I skip Step 5 (from above) before running my code a second time the appropriate text is generated in the proper List format (as it always is during the first run through)

    If there are other libraries or objects I could use instead of what I am currently using, I am willing to attempt a migration in order to solve my problem (Note: using a selection object instead of a range object to apply the list template does not solve my problem).

    Also, if there is any way to reset the Access or Word application (using VBA) to fool it into thinking the routine is being run for a first time I am willing to attempt this as well (in order to retain my user's open Word documents, I would prefer to reset the Access application).

    Thanks again for your response, I was beginning to think I might not get one.  Any and all suggestions you might have would be greatly appreciated.

    Tuesday, April 10, 2012 4:25 PM
  • Citydreaming, sorry for the late reply, I tested on my side according to your steps and it was indeeded be reproduced. However, I found that it works well if the vba code is put under the word VBA IDE not the Access IDE.

    I haven't found a workaround for the problem, so I will involve other senior engineers to see whether they can give you some suggestions or workarounds about the problem. There might be some delay about the response. Appreciate your patience.

    Best Regards,

    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, April 12, 2012 9:10 AM
  • While waiting for Bruce's reply I've begun exploring alternative technologies that may allow me to accomplish my goals without using the COM Interop.  Particularly, I've been looking into the Open XML specification and its capabilities.  As I said in my original post I'm relatively new to Microsoft technologies, but after spending quite a bit of time in the Open XML Developer Center I don't feel any closer to utilizing the technology to create a MSWord document from scratch.  The articles in the developer center talk a lot about making mass edits to already existing Word documents, but don't talk much about programatically creating brand new documents.  Furthermore, my entire application was built in Access, and after researching on the internet (and trying it myself, just to be sure) it appears that there is no way to load the Open XML SDK into Access for use with VBA, which leaves me with only the System.IO.Packaging namespace (which I gather can be used with VBA, although I haven't tried it).

    I was wondering if anyone had any advice on resources I could use to really get started on creating Word documents using Open XML, specifically techniques or resources about using the System.IO.Packaging namespace or VBA (that will thusly allow me to keep my current Access application).  Short of that I will probably need to migrate my application into Visual Studio, although I recognize that this particular forum is not the place to be seeking advice on doing so.

    If I have misunderstood the capabilities of the Open XML specification, and it is not suitable to creating Word documents from scratch, please let me know since I will cease trying to learn the technology if that is the case.

    Thank you for considering my problem, as always let me know if there is any additional information that you may need.

    P.S. - People experiencing a similar error to the one I am having trouble with may be interested to know some (minor) subsequent things that I have tried which have not solved the problem, thus hopefully you won't have to use your time trying them as well:

    1. I attempted to use a VBA.Reset command in various locations inside my code to no success.

    2. I attempted to use Access.Application.DoCmd.RunCommand acCmdReset in various locations inside my code; this also produced no success.

    3. I decided to try using VBA to save and close the Word document programatically, rather than leaving the document open and waiting for the user to save and close it themselves.  I prompted the user with a FileDialog to allow them to set the location and name of the save file, generated the file, and then saved it and closed it as per the user's input.  However, this also did not solve the error of formatting being lost if I tried to run my report again.

    Thursday, April 19, 2012 10:46 PM
  • You are correct that the OOXML SDK can't be access from within VBA. But an Addin can be built using VS that will give access to the SDK from within Access. It is quite feasible to build a Word document from scratch using the OOXML SDK.

    There a many articles on MSDN and this is another great resource

    you noted above that you wanted to avoid using Word templates. I'm not sure why that is. All Word documents are based on some template and using your own template makes life much easier.

    As you've experienced programmaticaly creating an multi-level list is challenging and frustrating. It would be so much simpler to design a template using Paragraph Styles that include the list levels and then applying the style to the range as you build the document.

    When I used your macro with a custom template the list numbering worked every time.

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Harold Kless Microsoft Online Community Support

    Friday, April 20, 2012 4:50 PM