select a range of excel cells

avikerem

Forums

Hi Brian
I was trying to perform from an Idea script some manipulations on excel cells. most of the manipulations works fine, but I could not find the equivalent to the folloing VB line:
 ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
this line select one row of excel cells from the current active cell to the rightmost non empty cell.
Naturally I tried the following, but it yield an error:
Set ex = CreateObject("Excel.Application")
Set wb = ex.Workbooks.Open (client.WorkingDirectory & "Results.ILB\Blahblah.XLSX")
wb.Sheets(1).Select
ex.ActiveSheet.Range("A1").Select
 ex.ActiveSheet.Range(ex.Selection, ex.Selection.End(xlToRight)).Select
 
Any idea how to obtain this functionality?
 
THANKS!

Brian Element Tue, 07/21/2015 - 14:57

Hi Avi,

One problem is that IDEAScirpt doesn't know what the Excel constants are.  I usually look them up in the excel object browser.  So without trying the code, IDEA wouldn't know what the xlToRight is, try replacing it with -4161 as that is what the Excel object browser shows it equal to or you can set it as a constant at the beginning of the script.

Const xlToRight = -4161

Let me know if that fixes the problem, if not I will play with it some.

Brian

avikerem Tue, 07/21/2015 - 15:11

thanks Brian. Surely I  am using some excel constants in the code and indeed some of them I  remembered to redefine and some I forgot. not a good practice. Eh?
I will fix this and will let you know tomorrw. good night..