how to define a range object in VBscript

Question

I'm trying to convert VBA program to VbScript in order to run it from the cmd. I created a program in VBA that get an excel workbook and um-marged cells while keeping its content in all the newly formed un-marged cells. explanation: the balded zeros are in one merged cell and the one are in a non-merged cells

[[0][0]][0][0]][1][1][1]

After the function I expect the result to look like:

[0][0][0][0][1][1][1]

each value in one single cell

I wrote a VBA script that doing it

this function take one sheet and un-marge all the cells while keeping the values for all of the newly formed cells

Sub UnMergeFill(ByVal ws As Worksheet)

    Dim cell As Range, joinedCells As Range

    For Each cell In ws.UsedRange
        If cell.MergeCells Then
            Set joinedCells = cell.MergeArea
            cell.MergeCells = False
            joinedCells.Value = cell.Value
        End If
    Next

End Sub

this function takes the first function and applys it for all the sheets in the workbook

sub UnMergeFillAllSheets()
    Dim ws As Worksheet
    for Each ws In Worksheets
         UnMergeFill ws
    Next
End Sub

when I'm converting it to VBScript its looks like:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Dim myworkbook, worksheetcount,cell
Set myworkbook = objExcel.Workbooks.Open("C:\Users\sm\Documents\work\try\try.xlsx")
worksheetcount = myworkbook.Worksheets.Count
Msgbox ("there are:"&worksheetcount&" sheets")'testing
set cell = CreateObject("Range")
For i= 1 To worksheetcount
    set ws = objExcel.Worksheets(i)
    ws.Activate
    for each cell in ws.UsedRange

    **take all merge cells and un-merge them**

    Next

Next

my problem is that I need to refer a range of merged cells, when I'm trying to create a range object I'm getting an error:

activeX component can't create object:'Range'

I know Range exists because I used TypeName(ws.UsedRange) and the object is range type.

Thank you for your help

Set objExcel = CreateObject("Excel.Application") should be your only use of CreateObject(). Other than that, let the Excel object model do the necessary object creation. — John Coleman
for each cell in ws.UsedRange doesn't need cell to be a previously defined object at all. That loop will iterate over the range. You should simply drop the line in which you try to define cell as a Range object. — John Coleman

Chronocidal Chronocidal · Accepted Answer · 2019-05-30T14:13:17

You are using Late Binding. The correct form would be to just Dim cell, and set it to Range objects. You will not get any debug help if you try to do something with it that Range objects cannot do, much like you will not get IntelliSense for it.

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

Dim myworkbook, worksheetcount, cell

Set myworkbook = objExcel.Workbooks.Open("C:\Users\sm\Documents\work\try\try.xlsx")
worksheetcount = myworkbook.Worksheets.Count

Msgbox ("there are:"&worksheetcount&" sheets")'testing

For Each ws In objExcel.Worksheets
    ws.Activate
    For each cell in ws.UsedRange.Cells 'Loop through Cells, not Rows or Columns

        **take all merge cells and un-merge them**
    Next cell
Next ws

how to define a range object in VBscript

1 Answers