Solved

VBA Documents.Add returns 'nothing' when executed outside the debugger

Posted on 2013-06-19
12
365 Views
Last Modified: 2013-07-15
I have a problem when trying to open a Word document via VBA code within Excel. The office environment is hosted as a VM (Office 2010 running on a virtual Windows 7 environment)

I've reduced the code to bare minimum to illustrate the problem. Code follows:

Dim NewDoc as Word.Document
Dim wdApp as Word.Application

set wdApp = New Word.Application
set NewDoc = wdApp.Documents.Add
wdApp.ActiveDocument.SaveAs "New.docx"
wdApp.ActiveDocument.Close
wdApp.Quit

...  clean up 

Open in new window


Executing the code via Excel VBA (not shown here) crashes with error 4248 "This command is not available because no document is open."

The line that is attempting to save the document is failing. Entering the debugger and inspecting NewDoc reveals that 'NewDoc = Nothing'.

Placing a breakpoint on the Documents.Add line and executing the code within the debugger, I can see that NewDoc is initialized to "Document1". Running the remaining code within the debugger correctly saves a Word doc named 'New.docx'!

If I port the code to a 'thick client' and run it locally it works fine. So, the problem is related to running within the VM.

Got me baffled :)    Any ideas out there?
0
Comment
Question by:XCalRocketMan
  • 5
  • 3
12 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 39261491
Try replacing


Dim NewDoc as Word.Document
Dim wdApp as Word.Application

set wdApp = New Word.Application

with


Dim NewDoc as object
Dim wdApp as object

set wdApp = createobject("Word.Application")
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39262035
Note

The underlying assumption to my suggestion is that in the VM the libraries may not be available / accessible and that switching to late binding as in the suggestion will circumvent that possible cause making it worth trying.

Chris
0
 

Author Comment

by:XCalRocketMan
ID: 39262235
I had thought it might be a binding issue, and although I thought I tried this before I recoded this morning and ... now I get another error.  This time the complaint is on the createobject call with error # 429: ActiveX component can't create object.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39262274
Are you sure your VM has word installed?

Chris
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:XCalRocketMan
ID: 39262353
Yep.  We are using a 'thin client' setup here at work. All our apps are hosted on the VM.  The Office Suite, and a ton of others. The binding issue sounded like a winner - remember I can run the code just fine from within the debugger - the original code I posted works. But outside the debug session, no joy.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39262454
I do not work with thin / thick clients so cannot be sure here but on the client try:

Re-registering Word:
    In the run dialog box type:
         C:\Program Files\Microsoft Office\Office\Word.exe /regserver
         Press OK

Chris
0
 

Author Comment

by:XCalRocketMan
ID: 39266567
Hi all,

Looks like this issue might be related to a Security Posture set by the VM administrators. Thanks for all the insight provided. I'll need to bark up another tree to get this resolved :)

Mike
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39326645
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39326646
Original request was:

'Executing the code via Excel VBA (not shown here) crashes with error 4248 "This command is not available because no document is open."'

in POST 39262235 this seemed to resolve the specific issue as the error changed to "error # 429: ActiveX component can't create object"

In post 39266567 it was stated that this latter error was caused by "Security Posture set by the VM administrators".

I am at a loss as to why there is insufficient data available and suggest the post at 39261491 was a valid solution for the question even though the asker thought they had tried it earlier as the error changed to a different install specific at that time.

Chris
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add a task in Outlook from access 11 35
C# code editing and collaboration 3 99
Surface Book vs Surface Pro 4 56
List of 'object codes' used by Find/Replace in Word 4 20
The purpose of this article is to demonstrate how we can use conditional statements using Python.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now