Excel not closing

A

A1pro

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
 
S

Shauna Kelly

Hi A1pro

For information on early and late binding, see
Early vs. Late Binding
http://www.word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm

For what it's worth, you're using early binding.

You basically have two lots of code here - some is manipulating Excel and
some is manipulating Word. From what I can see, most of the code is dealing
with Excel, and your code is running from within Excel. So, I suggest that
you ask the Excel newsgroup (eg microsoft.public.excel.programming) for help
with the Excel side of the code.

Once you have that sorted out, come back here and deal with the Word parts.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top