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:
Qualified code:
Instead of excel.Application I have also experimented with
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.
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"
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)
sub4 - closes word and excel. this seems to be working at least most of the time.
Thanks in advance this error on every other run thing is really driving me crazy.
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
PHP:
Dim excelApp As excel.Application
Set excelApp = excel.Application
excelApp.Worksheets(sheet_string).Range("A2:C10000").Clear
set excelApp = nothing
PHP:
CreateObject("Excel.Application")
GetObject(, "Excel.Application")
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
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
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
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