Error when working with Word and Excel

[Soniaski]

Hi Everybody,

I am having a problem when formatting Excel and Word documents utilizing VBA. Everything works great for the first go round. For the second run of the process I get a run time error 462 "The remote server or machine is not available or does not exist". I have stepped through the process and checked my task manager to make sure that all instances of Word and Excel close when I finish the first go round (that was a fun task ). The program always stops at the same point of formatting the Word Document. I have colored the stopping point in red in the code below. The code is a little clunky, but I've been playing with it to allow for the closing of Excel in the Task Manager.

I can exit Access and restart the program and it works fine for the first go round, but not the second.

Any thoughts?

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

Set Xl = New Excel.Application
Set XlBook = GetObject(vFilePath)
Xl.Visible = True
XlBook.Windows(1).Visible = True
Set XlSheet = XlBook.Worksheets(1)
XlSheet.Cells.Select
XlSheet.Cells.WrapText = True
XlSheet.Cells.Font.Name = "Calibri"
XlSheet.Cells.Font.Size = 10
XlSheet.Cells.EntireColumn.AutoFit

XlSheet.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
XlSheet.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
XlSheet.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
XlSheet.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
XlSheet.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

XlSheet.Range("a1").Select
XlSheet.Range("a1").FormulaR1C1 = vWhatItem
XlSheet.Range("b1").Select
XlSheet.Range("B1").FormulaR1C1 = rs.Fields(0)
XlSheet.Range("a3").Select
XlSheet.Range("a3").FormulaR1C1 = rs2.Fields(1) + " Ext. " + rs2.Fields(4)

XlSheet.Range("a2").Select
XlSheet.Range("a2").FormulaR1C1 = "Sales Rep: " + Trim(vRep)
XlSheet.Range("a4").Select
XlSheet.Range("a4").FormulaR1C1 = "Current Mbase: $" + Trim(Str(vOldMbase))
XlSheet.Range("a5").Select
XlSheet.Range("a5").FormulaR1C1 = "New Mbase: $" + Trim(Str(vNewMbase))

Set rs3 = dbs.OpenRecordset("Select MT9270 from [MRC400MFG_SF09270] where IMPN = '" & vWhatItem & "'")
rs3.MoveLast
rs3.MoveFirst

vInventory = rs3.Fields(0) * 30
vEffectiveDate = Date + vInventory
vEffectiveDate = Format(vEffectiveDate, "mm/dd/yyyy")

XlSheet.Range("b5").Select
XlSheet.Range("b5").FormulaR1C1 = "Effective Date: " + Trim(Str(vEffectiveDate))
Xl.ActiveSheet.PageSetup.LeftMargin = 0.25
Xl.ActiveSheet.PageSetup.RightMargin = 0.25
Xl.ActiveSheet.PageSetup.PrintGridlines = True

XlSheet.Columns("A:A").ColumnWidth = 35.03
XlSheet.Columns("B:B").ColumnWidth = 35.03
XlSheet.Columns("C:C").ColumnWidth = 20.03
Do While vwhatline <= vRecCount
StrSql = Trim(Str(vwhatline * 2) + 7)
myrange = "a" + StrSql + ":C" + StrSql
XlSheet.Range(myrange).Select
XlSheet.Range(myrange).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
vwhatline = vwhatline + 1
Loop
XlSheet.Range("A1:c7").Select
XlSheet.Range("A1:c7").Font.Bold = True
XlSheet.Range("A1:c7").Font.Size = 12
XlSheet.Cells.Select
XlSheet.Cells.WrapText = False
XlSheet.Cells.WrapText = True
XlSheet.Cells.Copy

Dim objWord As Word.Document
Set objWord = GetObject(vECNFormPathNew & vECNFormNameNew, "Word.Document")
objWord.Application.Visible = True
objWord.Application.Activate
objWord.Application.ActiveDocument.Unprotect
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.TypeText Text:=rs2.Fields(1)
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.TypeText Text:=rs2.Fields(2)
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.TypeText Text:=rs2.Fields(3)
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.TypeText Text:=rs2.Fields(4)
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.TypeText Text:=vWhatItem
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.TypeText Text:=rs.Fields(0)
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveRight Unit:=wdCell
objWord.Application.Selection.MoveDown Unit:=wdLine, Count:=5
objWord.Application.Selection.TypeText Text:="See Attached Form"
objWord.Application.Selection.EndKey Unit:=wdStory
objWord.Application.Selection.TypeParagraph
objWord.Application.Selection.TypeParagraph
objWord.Application.Selection.ParagraphFormat.LineSpacing = LinesToPoints(1)
objWord.Application.Selection.TypeParagraph
objWord.Application.Selection.TypeParagraph
objWord.Application.Selection.TypeParagraph
objWord.Application.Selection.MoveUp Unit:=wdLine, Count:=2
objWord.Application.Selection.PasteExcelTable False, False, False

objWord.Save
objWord.Application.Quit
'objWord.Close
rs.Close
XlBook.Save
XlBook.Close
Xl.Application.Quit
Set Xl = Nothing
Set XlSheet = Nothing
Set XlBook = Nothing
Set objWord = Nothing

 

[Soniaski]

Just trying to revive this in case someone has been off the board.

In doing a little more testing. If I remove all references to Word, it works perfectly. It must be something in my Word logic?

 

[Soniaski]

Just solved it.... I simply took out the line that was erroring out. The word document is still formatting correctly. Doh! That's what I get for just copying the Word logic from the Macro and not making sure of what each step did.

Still not sure why it bombed out on that line during the second run of the application and not the first, but I'm not going to worry about it for now.

 

[boblarson]

Still not sure why it bombed out on that line during the second run of the application and not the first, but I'm not going to worry about it for now.

Read this and it will explain (the same concept applies to all Office products):
http://www.btabdevelopment.com/ts/excelinstance

So the line you were having trouble with should have been:

objWord.Selection.ParagraphFormat.Line Spacing = objWord.LinesToPoints(1)

You can't have anything that is not connected somehow to your instantiated object. As the article I quoted (wrote) says, if you do so then a hidden instance opens and that is why it causes problems because it doesn't close until you close Access or kill it in the Task Manager.

EDIT: And you can get rid of all of these .Application. parts because the objWord IS the application object so it is redundant.

 

[Soniaski]

Amazing how such a little nuance can cause such a big problem. Thanks Bob. What a wonderful article.

I'm making the suggested changes right now.

I hope you know how much a lot of us appreciate your guidance....