Convert VB to Object Rexx

Convert VB to Object Rexx

Post by jerry chap » Mon, 08 Nov 2004 00:36:57


I have a VB program which creates an Excel file and am trying to convert it
to Object Rexx. I have converted a lot of it, but I am having trouble with
the following:

VB
iNumQtrs=2
Set oResizeRange = oWS.Range("E1", "E1").Resize(ColumnSize:=iNumQtrs)

Object Rexx
iNumQtrs=2
oResizeRange=oWS~Range('e1','e1')~Resize(ColumnSize=iNumQtrs)

The Object Rexx code get the following error:
<Object method not found>

Where am I going wrong?
 
 
 

Convert VB to Object Rexx

Post by rony » Mon, 08 Nov 2004 02:16:45


The above statement tells VB to use the argument in the method resize() which is called
"ColumnSize". Probably resize() needs more than one argument; if so, then in Object Rexx you would
just leave those arguments out but must use commas to indicate which argument you are supplying:

oResizeRange=oWS~Range('e1','e1')~Resize( , iNumQtrs)

The resize() function/method of a Range object expects two arguments, hence its signature in full
is: Resize(RowSize, ColumnSize). If you supply the ColumnSize argument only, then you must indicate
that you leave out the first argument.


Your version "Resize(ColumnSize=iNumQtrs)" would first compare the string "COLUMNSIZE" to "2", which
yields the false value "0"!

Hope that helps,

---rony

 
 
 

Convert VB to Object Rexx

Post by jerry chap » Mon, 08 Nov 2004 03:21:55

Thanks, that worked, but I found another problem.
I was passing oWS to a procedure as an argument,and that didn't work. When I
exposed oWS it worked. How can I pass oWS and keep the object type?



it
with
"E1").Resize(ColumnSize:=iNumQtrs)
which is called
in Object Rexx you would
argument you are supplying:
hence its signature in full
only, then you must indicate
"COLUMNSIZE" to "2", which
 
 
 

Convert VB to Object Rexx

Post by jerry chap » Mon, 08 Nov 2004 03:54:05

That worked great.
Now how do I convert the following:

Set oChart = oWS.Parent.Charts.Add
With oChart
.ChartWizard oResizeRange, xl3DColumn, ,xlColumns
.SeriesCollection(1).XValues = oWS.Range("A2", "A6")
For iRet = 1 To iNumQtrs
.SeriesCollection(iRet).Name = "=""Q" & Str(iRet) & """"
Next iRet
.Location xlLocationAsObject, oWS.Name
End With




it
with
"E1").Resize(ColumnSize:=iNumQtrs)
which is called
in Object Rexx you would
argument you are supplying:
hence its signature in full
only, then you must indicate
"COLUMNSIZE" to "2", which
 
 
 

Convert VB to Object Rexx

Post by jerry chap » Mon, 08 Nov 2004 04:33:42

I also had a problem translating the following:

' Move the chart so as not to cover your data.
With oWS.Shapes("Chart 1")
.Top = oWS.Rows(10).Top
.Left = oWS.Columns(2).Left
End With





convert
then
string
 
 
 

Convert VB to Object Rexx

Post by rony » Mon, 08 Nov 2004 06:21:43


This is in principle the same problem as your earlier posting:




What you see is a specific feature of Visual Basic which has gone in VB.Net (which is *NOT*
compatible to VB, believe it or not).

The following

> ' Move the chart so as not to cover your data.
> With oWS.Shapes("Chart 1")
> .Top = oWS.Rows(10).Top
> .Left = oWS.Columns(2).Left
> End With

can be recoded in Visual Basic (without the "WITH" statement) as:

set sh = oWS.Shapes("Chart 1")
sh.Top = oWS.Rows(10).Top
sh.Left = oWS.Columns(2).Left

and that can then be translated easily to Object Rexx:

sh = oWS~Shapes("Chart 1")
sh~top = oWS~rows(10)~top
sh~left = oWS~columns(2)~left

---

Using the same principle you could translate the following VB code:

>> Set oChart = oWS.Parent.Charts.Add
>> With oChart
>> .ChartWizard oResizeRange, xl3DColumn, ,xlColumns
>> .SeriesCollection(1).XValues = oWS.Range("A2", "A6")
>> For iRet = 1 To iNumQtrs
>> .SeriesCollection(iRet).Name = "=""Q" & Str(iRet) & """"
>> Next iRet
>> .Location xlLocationAsObject, oWS.Name
>> End With

into Object Rexx as follows:

oChart = oWS~parent~charts~add
oChart~chartWizard( oResize Range, xl3DColumn, , xlColumns )
oChart~seriesCollection(1)~xValues = oWS~range("A2", "A6")
do iRet=1 to iNumQtrs
oChart~seriesCollection(iRet)~name= '="Q' || iRet || '"'
end
oChart~location(xlLocationAsObject, oWS~name)

Hope that helps,

---rony
 
 
 

Convert VB to Object Rexx

Post by jerry chap » Mon, 08 Nov 2004 11:04:31

Thanks that fixed everthing, except I still have one problem. I can't close
and save the excel file I have created. The VB program leaves excel open for
the user to close. In a Rexxla prensatation I found the following code to
save a workbook:

myxl~application~displayalerts=.False
myxl~wbs~SaveAs('exq.xls') -- error rc=97 (object method not found)
myxl~workbooks(1)~close(SaveAll)
quitit=myxl~quit






VB.Net (which is *NOT*
 
 
 

Convert VB to Object Rexx

Post by Lee Peedi » Mon, 08 Nov 2004 11:51:49

erry,
I am emailing you an Excel demo I presented at the 2001 RexxLA
Symposium and also at Share 2003. There are several examples in there
that I think may be helpful (including the saveas routine).

Lee


On Sun, 07 Nov 2004 02:04:31 GMT, "jerry chapman"
< XXXX@XXXXX.COM > wrote: