Excel VBA "Range(Cells(X,X), Cells(Y,Y)).Select

[Grovkillen]

I cannot get this to work with my VB call.

This work within the VBA modul in Excel:

Code: Select all

Range(Cells(2, 2), Cells(40, 40)).Select

But this will not work when I run VB through MS:

Code: Select all

...
Set ExcelApp = CreateObject("Excel.Application")
    Set MyBook = ExcelApp.Workbooks.Open(filename)
    Set MySheet = MyBook.Worksheets(sheetname)
MySheet.Range(Cells(2,2), Cells(40,40)).Select
...

[Grovkillen]

I ended up doing this:

Code: Select all

MyRange = MySheet.Cells(40, 40).Address
    MySheet.Range("B2:" & MyRange).Select

This would equal to:

Code: Select all

Range(Cells(2, 2), Cells(40, 40)).Select

[Grovkillen]

Just found out that this would work...

Code: Select all

MySheet.Range(MySheet.Cells(2, 2), MySheet.Cells(40, 40)).Select

I.E. the Cells command need to have "MySheet" specified ...

[zabros2020]

Depends on what you want to do, could do something like this...

Code: Select all

Let>localFileDir=C:\Temp\temp.xls
Let>sheetName=Sheet1
XLSheetToArray>%localFileDir%,%sheetName%,sheetArray
'Select range
Let>rRow=40
Let>rCol=40
'Select first row and column to start from
'if 1, this will select 2
Let>fRow=1
Let>fCol=1
GoSub>SelectRange
MessageModal>%cText%

SRT>SelectRange
  Let>cText=
  Let>row=%fRow%
  Repeat>row
    Add>row,1
    Let>col=%fCol%
    Repeat>col
      Add>col,1
      Let>sText=sheetArray_%row%_%col%
      Position>sheetArray_,%sText%,1,pos
      If>pos=0
          ConCat>%cText%,%sText%
          If>col=rCol
            ConCat>%cText%,%CRLF%
          EndIf
      EndIf
    Until>col,%rCol%
  Until>row,%rRow%
END>SelectRange

Or if you know exactly where 1 value is that you need:

Code: Select all

Let>localFileDir=C:\Temp\temp.xls
Let>sheetName=Sheet1
XLSheetToArray>%localFileDir%,%sheetName%,sheetArray
Let>row=2
Let>col=2
Let>sText=sheetArray_%row%_%col%

[Grovkillen]

Thanks for your ideas but in my case I create a new Excel-file and simultaneously add conditional formatting to parts of my sheet.

[Marcus Tettmar]

The "Cells" object belongs to the "Sheet" object which is why you need to do MySheet.Cells. Without doing that VBScript has no idea what you mean by Cells.