Application sometimes fails when accessing Word

[WT Cline]

Hi all - yes I hate that "sometimes" word too. But in this case, I cannot find any good reason for this failure. Must be said upfront - the application I describe below works perfectly....until it doesn't. So, the code itself is ok. But clearly the code is doing something that it eventually doesn't like, and I get errors. I am using the latest and greatest Office 365 ProPlus versions.

My application loops through a text file, reading/pulling various bits of information, to be written to a spreadsheet. Part of that information is a Word document file name. My program needs to read the Word file for more specific information. So, my code calls a function that:

1. creates a FileSystemObject (set FSO = CreateObject("Scripting.FileSystemObject)
2. creates a word app (set wdapp = CreateObject("Word.Application")
3. Using the FSO object, checks to see if the folder and file exist, if so,
4. opens the Word file via wdapp
5. reads/captures the specific data
6. closes the Word file
7. sets the file and Word app objects to "nothing"
8. returns to the calling subroutine.

Within a single .txt file, this function will be called up anywhere from 1 to 90 times. The function is calling UNIQUE word docs, by the way, not the same Word doc over and over.

When the application is finished scanning through the .txt file, it goes to the next .txt file and repeats this process. There are 60 .txt files this application reads in this way. The whole point of the program is go through all 60 .txt files, and print the results onto a large spreadsheet. In doing so, the code will open/read/close 100s and 100s of Word docs. As I have said, it is all working....until it "dies" on me.

This works great for somewhere around 100-150 "loops", over a couple of the .txt files. side note: reading thro9ugh the .txt file is alarmingly FAST! But it slows way down in this function, apparently during the object creation, checking for folders and files and opening of the Word file (a couple seconds at least). That is probably not an issue, but throwing it out there in case anybody thinks opening a Word file (these are small files, 1 - 3 pages, minimal text) shouldn't be so slow.

But nevermind speed for the moment. While running this program, at some point, it will seem as if the program has stalled...and finally I will get an error, always within this Function. The errors are around not being able to open the file, or that Word has a a problem and can't open the file, or that Office has an issue, that maybe the file is corrupt. They are not. I have checked them, they all open and read just fine, as expected.

So, I am thinking, might their be a limit as to how many times, in short succession I can open/close a Word file? I know, doesn't make sense to me either...

Before you ask, no, it does NOT fail on the same file each time. I have stopped at this error, gone to the actual file, opened it - no problem. And, I can start and stop my application using other text files, and therefore it will be checking a whole different set of Word docs, and the error will still occur. The files are always where they should be, appear to be available to open, and not locked (I check for that possibility in the code).

Based on this description, anybody have any ideas what I might look for, or other coding methods to avoid the constant createobject/open/read/close cycle? For example, is there a way to pass the objects via the Function, so I can create only one instance of the FileSystemObject and Word.app in the main sub, then use them many times within the function? not sure I know how to do that... but....willing to learn! Open to any ideas.

Many thanks
Terry

 

[Zack Barresse]

Hello there!

Let me start off by saying, I feel your pain. I've done similar coding projects in the past. Without knowing more or seeing your code, I can tell you there is mass inefficiency of creating/using/destroying these objects repeatedly over time. With hundreds, possibly thousands of iterations of this behavior, you can severely bloat the allocated memory available to VBA. This is my guess as to why your code is bombing, seemingly at random times, as memory is issued/utilized/released depending on the state of the machine/application.

While I'm not sure how you're utilizing a function to call this (Word) process, the answer to your question is yes, you can create the object one time, then do all of your loops using the same object, without having the need to keep destroying/re-creating. One way to do this would be create the Word object outside of your function call, then pass it as a parameter. Another option might be to set a property for it, then check that property during the function call, and ultimately destroying it upon completion of your routine (outside of the function call).

A side note for creating the Word application (or any application, really), I would recommend testing for the existence of it prior to creating it. Always better to latch onto an already running process than create a new one.

If you could post your code, it would be easier to give you specific examples, or re-work your code, to get you working more efficiently. Below is a small example of how it might work if using a custom property to house the Word application. The example below assumes you're using early binding for both the Scripting and Word application objects. You would run the 'ParentRoutine' which in turn would call the 'ChildRoutine' for each document, do stuff, then move on.

Option Explicit


Private pWordApplication As Word.Application
Private pWordAlreadyOpen As Boolean


Sub ParentRoutine()
    
    Dim FSO As Scripting.FileSystemObject
    Dim DocumentFile1 As Scripting.File
    Dim DocumentFile2 As Scripting.File
    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    
    Const TestPath1 As String = "C:\Users\Zack\Desktop\Test\test 1.docx"
    Const TestPath2 As String = "C:\Users\Zack\Desktop\Test\test 2.docx"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set WordApp = WordApplication
    
    ' Start doing stuff here.......
    ' Presumably this would be in a loop reading text file lines
    ' Word object is already created and re-used
    
    Set DocumentFile1 = FSO.GetFile(TestPath1)
    Call ChildFunction(WordApp.Documents.Open(DocumentFile1.Path))
    
    Set DocumentFile2 = FSO.GetFile(TestPath2)
    Call ChildFunction(WordApp.Documents.Open(DocumentFile2.Path))
    
    ' All finished up, do closing stuff...
    
    If Not WordAlreadyOpen Then WordApp.Quit
    Set WordApp = Nothing
    Set FSO = Nothing
    
End Sub


Function ChildFunction( _
    ByVal TestDocument As Word.Document, _
    Optional ByVal CloseFileAfter As Boolean = True, _
    Optional ByVal SaveBeforeClose As Boolean = False _
    )
    
    Debug.Print "Process file: " & TestDocument.Name
    ' do stuff
    
    If CloseFileAfter Then
        TestDocument.Close SaveBeforeClose
    End If
    
End Function


Public Property Get WordApplication() As Word.Application

    If pWordApplication Is Nothing Then
    
        On Error Resume Next
        Set pWordApplication = GetObject(, "Word.Application")
        WordAlreadyOpen = Not pWordApplication Is Nothing
        If Not WordAlreadyOpen Then
            Set pWordApplication = CreateObject("Word.Application")
        End If
        On Error GoTo 0
        
        Set WordApplication = pWordApplication
        
    End If
    
End Property


Public Property Set WordApplication(ByVal Value As Word.Application)

    Set pWordApplication = Value
    
End Property


Public Property Get WordAlreadyOpen() As Boolean
    
    WordAlreadyOpen = pWordAlreadyOpen
    
End Property


Public Property Let WordAlreadyOpen(ByVal Value As Boolean)
    
    pWordAlreadyOpen = Value
    
End Property

 

[WT Cline]

Zack - Awesome response! I haven't been able to get in and try your tactics, but I am almost certain yours is the far better way to go. Thanks so much, will send a follow up email with some results. I am feeling good again! Pretty sure this will work.

Terry

 

[Zack Barresse]

I have faith Terry! Keep in mind, there are memory limits. 32-bit Excel is, IIRC, a 2gb limit, much like that of Access. The sample above I posted shouldn't be assumed to clear all of the waters for you, but I'm sure it will help. Sometimes it even helps to throw in a "DoEvents" line when looping multiple processes. I'm looking forward to hearing the results.

 

[WT Cline]

Follow up - WOW! Not only is this concept working without the same mysterious fail - I've run around 650 records and counting with no errors - but the speed has been cut down from about 3 seconds per record to around slightly less than a second. That is huge when one considers the number of values and word files this routine needs to check. I learned a lot here - never knew what Early/Late binding was (I switched a few to Early, no idea if it makes any speed difference, but I did notice the Intellisense update, that's nice). Learned how to pass the object via a function. I did not use your Property examples, that still baffles me....next project!

Thanks again, huge huge huge help!

Terry

 

[Zack Barresse]

Thanks for the follow-up, that is great news! I'm very glad it's worked out for you.

Binding
There are pros and cons for using Early vs. Late Binding. One big benefit is intellisense, as you've found. It makes building/debugging much, much easier. The big downside is portability. When that file travels to another machine, that reference will stay with it. Think of it as a static pointer to a resource file (pretty much literally what it is). If the user doesn't have the same version of Office you do, let's say they have an earlier version, those references won't exist and they'll get errors when trying to run your code, and when they look at the references it'll say "MISSING: xxx". So on a single machine, it works great.

One possible workaround, which I sometimes use in projects where I want Early Binding when I'm developing but want to switch to Late Binding for deployment, is Conditional Compilation. This is an advanced method a lot of Excel developers utilize. If you want to be a VBA pro, I would suggest reading this article. While the article leads up to a basis of conditionally compiling 32- vs. 64-bit API calls, it has a ton of useful information. You can always just do a web search for "excel vba conditional compilation". Some good stuff out there. So, what I'll do is name a compiler constant named "AuthoringWord". Then my code looks like this...

#If AuthoringWord Then
Dim WordApplication As Word.Application
Dim WordDocument As Word.Document
#Else
Dim WordApplication As Object
Dim WordDocument As Object
#End If

This is utilized when I'm developing as I set "AuthoringWord = 1". Then, when I'm done testing and all works well, I change it to "AuthoringWord = 0" before deployment (ensuring to test again...always testing). The downside with this is you have to teach yourself a new habit of checking your conditional compiler values. You can have more than one. I've had projects where I've done it with Word, PowerPoint, & XML, all in the same project.

Properties
These are mildly complex at first, but will change the way you code forever. They are super helpful. Although it's very short, I would read this for a decent understanding of what they are. Read that link carefully and thoroughly, it has a ton of information and is extremely succinct. These are mostly utilized in class modules, since you have to define all of your properties and methods, but can certainly be used anywhere in a VBA project. They are properties of their containers, either read or read/write. You can do all sorts of things with them and I'd highly recommend getting acquainted with them if you want to be an Excel dev pro.

Good luck Terry, and happy Excelling!!

 

[WT Cline]

Thanks again - Yes, I did think about Early/Late in the terms you mention, but since my team are all on the same version of Office 365, there shouldn't be any worries....he said, hopefully. And it REALLY does help, having the Intellisense available. Who the heck memorizes all the methods and what-not that could be available? Certainly not me. It is GREAT to be able to check to see what is available.

I will look into Properties as I am hooked by your phrase "...will change the way you code forever". How do I pass that up?!

To be clear, I am not paid to program. I am a Technical Writer who has been tinkering with VBA for quite a while, and have created all kinds of utilities our team uses to help us automate many really boring, tedious tasks that we commonly do. They are all based in Excel (we use Excel to track our projects), but often include linking data in an Excel sheet with data in a Word doc. And if I get my way, we'd extend our data into Access down the road. I love Excel for this, but I don't love Excel for advanced data management. For now, though, it is working really well.

The big program you just helped me with is an example of a type of thing we are asked to do. Without this code we would have to MANUALLY walk through over 60 HTML pages, gathering a bunch of information about the documents a user might read/download off our website. As I mentioned, each HTML source page can contain none, a few, or up to about 90 of these documents. I've so far counted over 2000 actual documents we would be checking manually. UGH! Let the computer do the same work in a tiny fraction of the time! and make it reusable/sustainable. Adding more HTML pages, with more documents linked? no problem! Only adds a few more seconds of processing time.

So, the somewhat rhetorical question becomes - how "pro" do I really need to be? The apps/functions I come up with are ONLY for us - not commercial in any form. That said, I enjoy writing solid code that won't (or shouldn't) fail. And that means the better I get, the more robust the code tends to get. But, again, it isn't really my job. I just do it because it is a) fun, and b) rewarding to see code work properly and deliver value.

Still, when I run into techniques that can make my coding easier/better/more solid, I will always take a look.

Many thanks again, super helpful!,
Terry

 

[Zack Barresse]

Sounds like a good place to utilize Early Binding for sure! This is eerily similar to how I learned VBA as well - utilizing Excel in-house for a company, which then led to building some custom solutions used in-house.

Properties are amazing. While they will definitely change the way you code, there is another which probably will so even more - class modules. There are so many different ways you can apply class modules. They're custom objects you can do basically whatever you want with. There's a bit of a learning curve, but it is certainly worth it. I suggest you start with small examples, trying to make something custom, and keeping it very small. That is, until you get the hang of it. Doing this will also give you a chance to better understand properties (and methods).

This is the smallest example of a property I can come up with. Try it out: put your cursor in 'TestIt' and press F8 to step through the code line-by-line. You'll see where it queries the property (Get) when setting to 'InitialTest', and how it writes to the property (Let) when setting it.

Option Explicit

Private pTest As Long

Public Property Get Test() As Long
    Test = pTest
End Property

Public Property Let Test(ByVal Value As Long)
    pTest = Value
End Property

Public Sub TestIt()
    Dim InitialTest As Long
    Dim FinalTest As Long
    InitialTest = Test
    Test = 42
    FinalTest = Test
    Debug.Print "Initial: " & InitialTest, "Final: " & FinalTest
End Sub

Your question of 'how pro do I really need to be' is an excellent one. What I generally tell people is you will get out of it what you put into it. Want to be the absolute best? No problem, just take on the heavy time commitment. We're probably looking at, for self-taught VBA, around 4-8 hours a day for the next 2+ years. Want to have a decent understanding and be able to create whatever you want? Ok, you're looking more like 1-4 hours a day for 6+ months. All of this is a total blind guess on my part, because the biggest unknown is you - the person doing the learning, and everyone learns differently. What works for me may not work for you, and vice versa. I'd venture to guess if you stuck with it, at your current level/rate, over a period of, let's say, 4 months ish, you'd probably be at least twice the developer you are now.

If you weren't a writer, I had you pegged for an engineer. You write very clearly and it's super easy to understand. It's a bit of a rarity. I'm glad I happened to be the one blessed with helping you lol.

Regarding your project, if you're opening up html files in Word and then reading them from Excel, there is actually a faster solution! We can utilize the FileScriptingObject to read the file as a text stream, then parse from there. You'll find working with objects will slow code down as opposed to working in memory (i.e. arrays, strings, numbers, bytes, etc.). In the example of your code here, you're opening Word - takes time and resources to allocate in memory, opening multiple documents - takes more time and resources to load the entire document and not just the text, and so on. We could probably improve this by several magnitudes if that's the case.

 

[WT Cline]

Regarding the app itself, I don't think I described what I did that made such a huge difference. As you properly suggested, the single biggest, most important change was to move the FileSystemObject piece OUT of the function, and back in the main module that calls it. That took a bit of coding (based on your excellent example code!!!) as I had to undue a bunch of stuff the function once contained. But the net is that the object is only created one time, so the Function can spend it's time evaluating the already opened Word file. And that is so much faster and far more robust and stable. Lesson learned: Don't open and close an objects zillions of times in the same application!

Another thing that keeps the speed up: I have learned to do as much as I can in arrays, rather than going back and forth between, say, another excel worksheet full of data I need. BILLION times faster than looking at each cell in a two dimension loop, over and over. So, here's an outline of the application:

In this same app, I also open a rather large .txt file that is actually the HTML source page I copied off the web site. Everything is done off-line in this case, so I download/save the HTML source file to a Unicode enabled .txt file, which my application will later read. Here is the basic outline of the entire application. Uh, yes, it has a lot more lines of code than below.

Dim FSO As Scripting.FileSystemObject 'These used to be inside the Function, now are set once, at top of program
Dim wdApp As Word.Application
Set FSO = New Scripting.FileSystemObject
Set wdApp = New Word.Application

List = Source.Range("A1").CurrentRegion ' Instant Array, one line of code, no nested loops needed! FAST! The Source worksheet contains a list of HTML/txt files to check. I use another program to update this list, on occasion, as it doesn't change very often. There are 60 .txt files I am currently evaluating

For i = 0 to Ubound(List)

FullFileName = FilePathStr & List(i, 3) 'where List(i,3) is the name of the .txt file I will process next
Set adoStream = New ADODB.Stream 'I just realized, I may not need to set a NEW adoStream each loop....I should be able to do that just once, before all the looping code...

With adoStream
.Open
.loadFromFile FullFileName
TextArr = Split(.ReadText, vbCrLf) 'Drops the whole txt file into an array using Split LOVE IT!
.Close
End With

'Then to evaluate the data, I run a nested For/next loop and get each line from the .txt like this:

For k = 0 to Ubound(TextArr)

DataLine = TextArr(k) 'so I crawl through the text file one line at a time, evaluating each line, looking for certain things. The files can have a LOT of lines, maybe over a thousand?

• evaluate data in DataLine, if useful, use it, or if not, loop to the next dataline and repeat ( my app looks for several specific values in each "loop".)
• if we find a very particular value, we pass and open via a function a Word Doc to find more info. 'This is the function you helped me fix

next k

next i
set objects to Nothing...


I admit I have been keeping Classes at an arms distance. I actually - more or less - know what they do, but I am not adept enough to understand how they would fit in my applications. The demos and tutorials are usually around very structured data, ie, a sales order, or a list of sales by customer, region, etc. So fairly simple to set up the class. My apps tend to be much less structured,. That said, if I was more familiar, I bet I could find ways to leverage Classes. Sigh...so much to learn, so little time.

Terry

 

[Macropod]

One possible workaround, which I sometimes use in projects where I want Early Binding when I'm developing but want to switch to Late Binding for deployment, is Conditional Compilation.

Another is to declare the named constants when making the switch from early binding to late binding. That way, the names give anyone reading the code the (usually) descriptive names rather than just obscure numbers.

Also, repeatedly opening & closing Word (or any other app) imposes significant overhead. It would be better to open Word once, at the start of processing, then leave it open until all processing is done. That way, one only needs to open/close the individual documents, which will be way faster.