Excel Word VBA Problem

[Matt]

I'm calling an excel macro from a word macro, everything was going swimmingly until I adapted the excel macro to encompass

Set myrange = Sheets("Master").Range("K2:K" & lastrow)

I have declared the variable, the excel object library has been referenced I have created the excel object

Dim Xl As Object
Set Xl = CreateObject("Excel.Application")

and I'm using a

With Xl.... ...end with piece of code

Like I say everything was working OK until I introduced the 'Set myrange...' element.

I now get an error 'Type Mismatch'

Any ideas, hope someone can help

thanks in advance

Matt

 

[KniteMare]

Where is lastrow defined in your code?

 

[Matt]

it's declared as an integer data type within the 'With XL' code, the variable is set just above the myrange variable as below.


Sheets("Master").Select

lastrow = Range("A65536").End(xlUp).Row

Have stepped through the code and it is picking up the correct value for lastrow.
This message was edited by Matt on 2002-10-24 12:37

 

[KniteMare]

Just using the snippit that is the problem child I get the expected result of K2:K5 being selected.

When your Word macro runs; does it Select/Activate the "Master" Sheet so that the reference is to the Excel Application and not the Word Application it was called from?

I'm on my way home. I've been teaching all day with out my partner so to wind down I am going to get in an hour run or so and I'll look back at this and try to duplicate the calls. It might be fun to try to solve and may end up addressing some areas I have been thinking of exploring anyway.

Yours in EXCELent Frustration

KniteMare

I set this as a test bed to start from.

'Code Follows:

'Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
lastrow = 5

Set myrange = Sheets("Master").Range("K2:K" & lastrow)
myrange.Select
'this selects K2 throught K5 when you click anywhere on the sheet

End Sub

'Code Ends: