Creating Word documents from Excel VBA

[dhancy]

Hello,

I have an Excel VBA script that creates multiple Word documents. I've been using it for a few years and it's been working great. I have roughly 350 files. The script used to create the Word documents and save them as PDFs in about 45 minutes.

In the last few months, it's slowed down considerably. Now it takes more than two hours.

I'm at a loss as to what changed. The only thing I can think of is that our organization upgraded to Office 2016 from Office 2013 over the summer. Could that be causing the problem?

Here is the code I am using to set this up:

    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document


    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = False                                      
            
    Set wrdDoc = wrdApp.Documents.Add(Template:=folderTemp & "\DD4.dotx")

Is there anything in there that needs to change for 2016? Or can you think of something else that might be causing the slow down? I appreciate any insight on this.

Thanks!

Dennis

 

[Kamolga]

To my knowledge this has nothing to do with your version but free RAM.

You can export a range or a sheet directly to pdf which I believe is quicker (quite instantenuous)

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\Users\YourName\Documents\Name_of_the_file.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False

 

[NdNoviceHlp]

Office 2016 is much slower than 2013. It takes a longggg time to load. If U can, just open Word once and leave it loaded until your done using the wb. HTH. Dave

 

[dhancy]

Interesting.. thanks!

Looking at my code, I do see that I am doing "CreateObject("Word.Application")" in the middle of the loop that's going through all 350+ files.

I am leaving for the day soon, but I will definitely look into changing the logic so that it only opens Word once.

Thanks for the idea. i will let you know how this goes.


Dennis

 

[Macropod]

Looking at my code, I do see that I am doing "CreateObject("Word.Application")" in the middle of the loop that's going through all 350+ files.

In which case you're creating 350+ instances of Word. Aside from the fact you don't need Word for what you're doing, as Kamolga says, with your present code you should instantiate Word (wrdApp) once - outside the loop. Indeed, depending on what you're doing with the Word document(s) inside the loop, you may even be able to instantiate wrdDoc once outside the loop also.

 

[dhancy]

So, I made the change in my code so that I am only instantiating wrdApp once. I then did a before and after comparison on a sample of five Word documents.

Before: 15 minutes, 19 seconds
After: 9 minutes, 6 seconds

If my math is correct, that is 40% faster. This is awesome!

I can't believe I didn't see this before. Thanks to everyone who responded.


Dennis