Can't applying Union Range from VB

[sergeos]

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

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:

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

If I write by one Range

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

all work.

Is it possible use anyway non-continues Ranges?

[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

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

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

[sergeos]

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

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.

you should always specify which sheet you want to work with

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

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?

[westconn1]

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

[sergeos]

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:

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

Did you try in Visual Basic 6.0 IDE?

[westconn1]

Did you try in Visual Basic 6.0 IDE?

yes on 2 computers