Sub won't open MS word (VBA)
I am trying to use a macro that builds reports in Word (this macro was not written by me, and also whoever wrote it still works here). Anyway, it works great on my colleagues' computer, but won't work on mine (I have a cursory pro 3 if that matters).
When I run the macro, it fails: Set VAVdoc = WordApp.Documents.Add
On failure, I get the following error:
Runtime error '91': object variable or with not specified block variable.
I'm at a loss why it won't work on my setup, but it will on my staff. Any ideas?
Any help is greatly appreciated. Update: after trying:
Set o = CreateObject ("Word Application")
again without o.quit I get the error
ActiveX component cannot create object
Dim i As Integer
Dim WordApp As Word.Application
Dim HVACdoc As Word.Document, VAVdoc As Word.Document, CDWdoc As Word.Document
Dim FullName As String, ShortName As String, TrendMonth As String, TrendYear As String, StartTrend As String, EndTrend As String
Dim ChartName As String, Directory As String, FolderName As String
Dim VAVName As String, VAVLocation As String, HVACName As String, HVACLocation As String, CDWName As String, CDWLocation As String
Call WorksheetCall("AHU-1")
FullName = "Mossman Building"
ShortName = "Mossman"
TrendMonth = MonthName(Month(Cells(4, 3)))
TrendYear = Year(Cells(4, 3))
StartTrend = Format(Cells(4, 3), "dddd, mmmm dd, yyyy")
EndTrend = Format(Cells(4, 3) + 6, "dddd, mmmm dd, yyyy")
Directory = "P:\M&V\- Projects\UNC-G\UNCG Year 7 Report"
FolderName = MonthName(Month(Cells(4, 3)), True) & " " & TrendYear
VAVName = FolderName & " - " & ShortName & " C.2.3.docx"
VAVLocation = Directory & FolderName & "\" & VAVName
HVACName = FolderName & " - " & ShortName & " C.2.4.docx"
HVACLocation = Directory & FolderName & "\" & HVACName
CDWName = FolderName & " - " & ShortName & " C.2.5.docx"
CDWLocation = Directory & FolderName & "\" & CDWName
On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If Err.number <> 0 Then
Set WordApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Call DefineDescriptions(TrendMonth, TrendYear, StartTrend, EndTrend)
'Report C.2.3 - VAV Conversion
If Dir(VAVLocation) = "" Then
Set VAVdoc = WordApp.Documents.Add
VAVdoc.SaveAs (VAVLocation)
End If
source to share
This is not a solution, but a hint for debugging.
First, in Task Manager, close all running Word applications.
To Tools - References
deselect all links to Microsoft Word ??? Object Library
.
Now try the following macro. Walk through it with F8
. But at the end also through .Close
and .Quit
. Because if you didn't, unused Word processes were collected on the system.
Sub testWordAppLateBinding()
Dim oWordApp As Object
Dim oWordDoc As Object
Set oWordApp = CreateObject("Word.Application")
oWordApp.Visible = True
Set oWordDoc = oWordApp.Documents.Add
oWordDoc.Close
oWordApp.Quit
End Sub
It works? Does Word open with a new document? If so, then late binding works. If not, what errors do you get?
Now in Tools - References
select the link Microsoft Word 14.0 Object Library
and try the following macro:
Sub testWordAppEarlyBinding()
Dim oWordApp As Word.Application
Dim oWordDoc As Word.Document
Set oWordApp = CreateObject("Word.Application")
oWordApp.Visible = True
Set oWordDoc = oWordApp.Documents.Add
oWordDoc.Close
oWordApp.Quit
End Sub
Does this work too? Does Word open with a new document? If this is the case, then so will early binding. If so, then the error is elsewhere. If it is not, but late binding works, then you need to change your code to late binding.
If nothing works, can you start Word manually at all? Does the word begin without dialogue? Or what dialogs were displayed?
source to share