Problem with setting Excel Range

[Alan]

Hi. I am accessing an Excel spreadsheet from VBA code running in
Word. However, I am unable to set a range of cells. (I want to copy
them to Word.)

This is the code I tried in Word:

Sub RangeStuff()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim TableRange As Range
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("sample.xls")
Set TableRange = ActiveSheet.Range(Cells(1, 1), Cells(3, 3))
TableRange.Select
xlWB.Close False
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

However, I get a "" error on the following line:

Set TableRange = ActiveSheet.Range(Cells(1, 1), Cells(3, 3))

However, the code below works fine in Excel VBA:

Sub RangeStuff()
Dim TableRange As Range
Workbooks.Open("sample.xls")
Set TableRange = ActiveSheet.Range(Cells(1, 1), Cells(3, 3))
TableRange.Select
End Sub

I have set up the reference to Excel in the Word VBA project, and
other references to Excel functions work.

Anybody see what I am doing wrong? Thanks in Advance,
Alan

 

[Doug Robbins - Word MVP, originally posted via msn]

It needs to be done as follows:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim TableRange As Excel.Range
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("c:\mybook1.xls")
Set xlSheet = xlWB.ActiveSheet
Set TableRange = xlSheet.Range(xlApp.ConvertFormula("R1C1:R3C3", xlR1C1,
xlA1))
TableRange.Copy
ActiveDocument.Range.Paste
xlWB.Close False
Set xlWB = Nothing
Set xlApp = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com