Unqualified Reference - Automating Word Excel

mbreu996

Registered User.
Local time
Today, 11:08
Joined
Feb 19, 2010
Messages
49
I am automating a task from an Access database. I am automating excel and word using vba.

I am experiencing "unqualified method calls" - where I'm making a hidden reference to the old instance of the application.
http://support.microsoft.com/kb/319832

Microsoft says to make all unqualified calls qualified by referring to the application object variable. I thought I changed my code accordingly, but I still get more and more errors.

Before I changed my code, when my references where unqualified, I got run-time error 462. Now I get run-time error 1004 "method of 'worksheets' of object '_Application' failed".

Unqualified code:
PHP:
Worksheets(sheet_string).Range("A2:C10000").Clear
Qualified code:
PHP:
Dim excelApp As excel.Application
Set excelApp = excel.Application
excelApp.Worksheets(sheet_string).Range("A2:C10000").Clear
set excelApp = nothing
Instead of excel.Application I have also experimented with
PHP:
CreateObject("Excel.Application")
GetObject(, "Excel.Application")
I think I am running into trouble because I am referencing the excel application in more than 1 sub. Here is an overview of how I have things set up.

public sub make_report()
sub1
sub2
sub3 'runtime error 1004 occuring every other run
sub4
End sub

sub1 - opens excel and word using qualified references. no issues with this so far.
PHP:
Public Sub sub1()
     Dim folder_string As String
     Dim excel_filename As String
     Dim wordFullPath As String
     Dim word_filename As String
          
     folder_string = "\\Items\"
     excel_filename = "Report_Plots.xlsx"
     word_filename = "Overturn_Report.docx"

     '   Late Binding (Needs no reference set)
    Dim oXL As Object
    Dim excelFullPath As String
     
     '   Create a new Excel instance
    Set oXL = CreateObject("Excel.Application")
     
     '   Full path of excel file to open
    excelFullPath = CurrentProject.Path & folder_string & excel_filename
     '   Open it
    With oXL
        .Visible = True
        .Workbooks.Open (excelFullPath)
    End With
     
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    
    wordFullPath = CurrentProject.Path & folder_string & word_filename
    Set wordApp = New Word.Application
    With wordApp
        .Visible = True
        Set wordDoc = .Documents.Open(wordFullPath, , False)
    End With
    
Set oXL = Nothing
Set wordApp = Nothing
Set wordDoc = Nothing

End Sub
sub2 - adds header text to the word document. this is how i first noticed my unqualified references, but now that I qualified is "bug-free"
PHP:
Public Sub Sub2(bookmark_name As String, mytext As String)Dim wordApp As Word.Application
Set wordApp = GetObject(, "Word.Application")
With wordApp
  .Selection.Goto What:=wdGoToBookmark, Name:=bookmark_name
  .Selection.TypeText (mytext)
End With
Set wordApp = Nothing
sub3 - pastes data from access to excel and copies excel charts to word. this is the one I still can't seem to fix (it works 1/2 the time, even the run-time error 1004 only pops up every-other run)
PHP:
Public Sub Sub3(sheet_string As String, myarray() As Variant, min_max() As Variant)
Dim column_string As String
Dim num_columns As Long
Dim min_num As Long
Dim max_num As Long
'On Error Resume Next

'determine paste dimensions
num_columns = UBound(myarray, 1) + 1
If num_columns = 1 Then
column_string = "A"
ElseIf num_columns = 2 Then
column_string = "B"
ElseIf num_columns = 3 Then
column_string = "C"
End If

'check min max
min_num = 1 * min_max(0, 0)
max_num = 1 * min_max(1, 0)

If min_num = max_num Then
min_num = min_num - 365
max_num = max_num + 365
End If

Dim excelApp As excel.Application
Set excelApp = excel.Application
excelApp.Worksheets(sheet_string).Range("A2:C10000").Clear
excelApp.Worksheets(sheet_string).Range("A2:" & column_string & UBound(myarray, 2) + 2) = _
WorksheetFunction.Transpose(myarray)

excelApp.Worksheets(sheet_string).ChartObjects(sheet_string).Activate
With ActiveChart
'change min max
    .Axes(xlCategory).MinimumScale = min_num
    .Axes(xlCategory).MaximumScale = max_num
    'copy chart
    .ChartArea.Copy
End With

Dim wordApp As Word.Application
Set wordApp = GetObject(, "Word.Application")

With wordApp
    'paste the chart
    .Selection.Goto What:=wdGoToBookmark, Name:=sheet_string
    .Selection.PasteAndFormat Type:=wdPasteDefault
End With

Set excelApp = Nothing
Set wordApp = Nothing
end sub
sub4 - closes word and excel. this seems to be working at least most of the time.
PHP:
Public sub sub4()
Dim excelApp As excel.Application
Set excelApp = GetObject(, "Excel.Application")
excelApp.Workbooks("Report_Plots.xlsx").Close False
excelApp.Quit

Dim wordApp As Word.Application
Set wordApp = GetObject(, "Word.Application")
wordApp.Quit wdDoNotSaveChanges

Set excelApp = Nothing
Set wordApp = Nothing
Thanks in advance this error on every other run thing is really driving me crazy.
 
Your problem is you keep instantiating new objects in each sub and trying to set them to the object you already have open.

Don't do it. PASS the object to the sub:

Private Sub MySub (objWord As Object)


then you can use objWord to refer to things and then when it hits the Exit Sub (or End Sub) it will pass it back to the passing procedure.
 
Thanks for the prompt solution to my long-winded post! Now that you point it out, what I was doing does appear to be very misguided. I will implement and post any further issues, but I'm guessing it should solve all my issues.
 
Simply amazing. Solved all my problems. Thanks Bob, my sleepless nights are over!
 
Glad we could help you out. :)

thumbsupsmile.jpg
 

Users who are viewing this thread

Back
Top Bottom