Referring to named Excel range from Word

[mantooth29]

I am automating Excel from Word, but am running into issues when trying to name a range that my For Each Next loop needs to run through.

I have tried fully qualifying the range 'xlattach' in many different ways, but get a variety of errors from "Method of... Global failed" to Application Defined Error.

Can someone tell me the proper way to set an excel range from a different application?

This line of code is the offender

Set xlattach = Excel.Application.ActiveWorkbook.Worksheets("Output").Range(xlattach)


Here is the full code

Sub Attach()


Dim xl As Excel.Application, wb As Excel.Workbook, xlattach As Excel.Range, xlfilepath As Excel.Range
Dim a As String
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set xlattach = Excel.Application.ActiveWorkbook.Worksheets("Output").Range(xlattach)
'--gave up on naming range in VBA, switched to a name in the workbook
'Set xlattach = wb.Range(xlattach, xlattach.End(xlDown))

Set wb = xl.Workbooks.Open("C:\Mail Merge Testing\-TestFile.xls")

With Selection
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "Doc2??"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchWildcards = True
.Execute
End With

'-- The Select with below was a test
With wb.Worksheets("Output").Range("Bk2")
.Select
End With
'-- End Test
For Each xlfilepath In xlattach
a = xlfilepath.Text
With ActiveDocument
Selection.InlineShapes.AddOLEObject ClassType:="AcroExch.Document.7", _
FileName:="C:\Mail Merge Testing\PDFs\" & a & ".pdf", LinkToFile:=False, _
DisplayAsIcon:=True, IconFileName:= _
"C:\Windows\Installer\_PDFFile.ico" _
, IconIndex:=0, IconLabel:=a & ".pdf"
End With
Next xlfilepath


End With


End Sub



I am pretty good with Excel VBA but am finding that communicating between two or more applications is a whole new challenge.

[macropod]

You can't do this:
Set xlattach = Excel.Application.ActiveWorkbook.Worksheets("Output").Range(xlattach)
Apart from the fact it's a circular reference, you have declared 'xlattach As Excel.Range', which is consistent with 'Set xlattach =', but 'xlattach' in Range(xlattach) must be a string.

I suggest you take a close look at some of the other threads here and at http://www.msofficeforums.com/word-vba/ for automating Excel from Word.

[mantooth29]

I appreciate the response macropod, I am adding that forum to my favorites.

I am also embarrassed that I treated the Excel range like a VBA variable. Whoops!

For anyone who may have the same problem, I ended up splitting each high level Excel object.

Before I had dimmed Excel
.Application
.Workbook
.Range

What I left out was treating the worksheet as a separate variable. My working code looks like this.


Dim xl As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, xlattrng As Excel.Range, xlfilepath As Excel.Range
Dim a As String

Set xl = CreateObject("Excel.Application")
xl.Visible = True

Set wb = xl.Workbooks.Open("C:\Mail Merge Testing\TEST.xls")
Set ws = wb.Worksheets("Output")
Set xlattrng = ws.Range("BK1")
Set xlattrng = ws.Range(xlattrng, xlattrng.End(xlDown))