Can't applying Union Range from VB

Hi,
i'm need combine several Ranges in one Range. So, code in VBA is working:

Code:

---

sAddress = "A4:R9,A11:R436,A439:R452,A454:R1326"
Set oRanges = Application.Range(sAddress)

---

all ok.

And, when i'm trying running code like this, the VB6.0 IDE generate error. My code is:

Code:

---

sAddress = "A4:R9,A11:R436,A439:R452,A454:R1326"
Set oRanges = oExcel.Range(sAddress)

---

If I write by one Range

Code:

---

sAddress = "A4:R9" 'or for example "A11:R436"
Set oRanges = oExcel.Range(sAddress)

---

all work.

Is it possible use anyway non-continues Ranges?

a range is a member of a worksheet, not the application, while you may get away with it in vba, or even in vb6 if there is currently an activesheet

this will not work

Code:

---

Set oexcel = CreateObject("excel.application")
sAddress = "A4:R9,A11:R436,A439:R452,A454:R1326"
Set oRanges = oexcel.Range(sAddress)

---

because there is no activesheet, in fact no open workbook

you should always specify which sheet you want to work with

Code:

---

Set oRanges = oexcel.workbooks("mybook.xls"),sheets("sheet1").Range(sAddress)

---

or use object variables for your workbooks and sheets

as you have only posted very limited code, i can only guess that this is probably your problem

Quote:

---

Originally Posted by westconn1

a range is a member of a worksheet, not the application, while you may get away with it in vba, or even in vb6 if there is currently an activesheet

this will not work

Code:

---

Set oexcel = CreateObject("excel.application")
sAddress = "A4:R9,A11:R436,A439:R452,A454:R1326"
Set oRanges = oexcel.Range(sAddress)

---

because there is no activesheet, in fact no open workbook

---

This code work fine in VBA instance. In VB only with continued Range. In fact I not need activesheet, i'm define virtual Range from Application methods.

Quote:

---

Originally Posted by westconn1

you should always specify which sheet you want to work with

Code:

---

Set oRanges = oexcel.workbooks("mybook.xls"),sheets("sheet1").Range(sAddress)

---

or use object variables for your workbooks and sheets

as you have only posted very limited code, i can only guess that this is probably your problem

---

Ok, sorry for this, please, see my example code

Code:

---

Private Sub test()
    Dim objExcel        As Object
    Dim objWorkbook    As Object
    Dim oRanges
   
    Dim sAddress        As String
   
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("c:\test.xls")
   
    ExcelWorkSheet = objWorkbook.Worksheets(1)
    objExcel.Visible = True
   
    sAddress = "A4:R436"
    Set oRanges = ExcelWorkSheet.Range(sAddress)      '<= work as worksheet member
    '(Set oRanges = objExcel.Range(sAddress)      '<= also work as application member)
   
    sAddress = "A4:R9,A11:R436,A439:R452,A454:R1326"
    Set oRanges = ExcelWorkSheet.Range(sAddress)      'ERROR Can't assign non-continues Ranges. In VBA code this part work correctly.
   
    Set objWorkbook = Nothing
    Set objExcel = Nothing
    Set oRange = Nothing

End Sub

---

Any ideas?

Quote:

---

Any ideas?

---

i pasted your code, changed file name, added set keyword for excelworksheet

all worked, without error for me

i retested in a different version of windows and excel

Quote:

---

Originally Posted by westconn1

i pasted your code, changed file name, added set keyword for excelworksheet

all worked, without error for me

i retested in a different version of windows and excel

---

My problem still in:

Quote:

---

And, when i'm trying running code like this, the VB6.0 IDE generate error.

---

Did you try in Visual Basic 6.0 IDE?

Quote:

---

Did you try in Visual Basic 6.0 IDE?

---

yes on 2 computers