Hello,
I'm running the following code from excel
Sub RunWordMacro_Automation()
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
'MsgBox ("The Active Row is " & ActiveCell.Row)
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open _
("\path\ word.doc")
WordApp.Visible = True
WordApp.Run "infofiller_echannels"
' Uncomment the next line of code to print the document.
' WordDoc.PrintOut Background:=False
' Uncomment the next line of code to save the modified document.
' WordDoc.Save
WordApp.Quit SaveChanges:=wdDoNotSaveChanges
Set WordApp = Nothing
End Sub
The infofiller_echannels macro called from excel is:
Sub Infofiller_echannels()
'Both Excel, Word and Outlook must be early bound. No idea what this
actually means but it must be done for each one.
'e.g. on vba editor go to tools --> References --> select Microsoft Word
object library for MS word
'Msg = "Enter Excel Active Row Number" ' Get the row number from the
spreadsheet
'ans = InputBox(Msg)
Dim filepath
Dim ans As Long
Dim oExcel As Excel.Application
Dim osheet As Excel.Worksheet
filepath = "\path\test_echannels.xls"
Set oExcel = CreateObject("Excel.Application") 'opens excel
oExcel.Workbooks.Open (filepath) 'opens document refered to
in filepath
Set osheet = oExcel.ActiveWorkbook.Worksheets(1) 'sets sheet 1 as
activeworkbook
With osheet
ans = .Cells(.Rows.Count, "A").End(xlUp).Row ' Checks for first nonempty
cell on column A starting from last row
End With
oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.Close ' Closes active workbook
oExcel.Quit ' Closes excel
Set osheet = Nothing
Set oExcel = Nothing
'subroutines used to populate the fields in the Request to Apply build
Document
'Note the _e at the end refers to echannels excel template
'ans is the rownumber and filepath is the excel spreadsheet file path
Call notimportant
Call Dept_e(ans, filepath)
end sub
Sub Dept_e(rownumber, filepath)
Selection.GoTo What:=wdGoToBookmark, Name:="Dept"
Dim oExcel, osheet
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open (filepath)
Set osheet = oExcel.ActiveWorkbook.Worksheets(1)
Selection = osheet.Range("I" & rownumber)
If IsEmpty(osheet.Range("I" & rownumber)) Then ' If cell is empty the sub
will end and
' send control back to main sub
oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.Close
oExcel.Quit 'This closes the excel
worksheet opened
msg = MsgBox("Missing Critical Information" & vbNewLine & "Fill in Column I
on the Excel Worksheet" & vbNewLine & _
"Save the Worksheet" & vbNewLine & "Then Hit Sendout Again", vbCritical)
Set oExcel = Nothing
End
Else
Selection.Copy
End If
oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.Close
oExcel.Quit
Set osheet = Nothing
Set oExcel = Nothing
end sub
The dept_e sub looks at column I and if there is data in that column it puts
it on the word document, if there isn't it gives a message and then stops.
The thing is that for some reason there is an instance of excel running, that
does not seem to close.
The odd thing is that if I run the infofiller_echannels macro from word it
closes all instances of excel. However, if I running it from excel, it seems
to leave the two instances of excel running ( It should leave one running)
Any ideas why this might be?
TIA