cannot convert oleobject in any variable to integer

Greetings All,

I have some powerbuilder code that opens a spreadsheet and writes out some information.  My problem is that I want to add additional sheets in Excel and then make them active.  The example code that I have seen always uses a number to refer to a spreadsheet as follows:

xlsub = xlapp.Application.ActiveWorkbook.Worksheets[1]

That works fine.  But now I am trying to substitute the number '1' for a variable called newsheet.  I am getting an 'ANY' / 'LONG' conversion error.

Here is my code:

//now add additional sheets and rename the tabs to coincide with the voucher number
any newsheet
string voucher
      For r = 1 to numrows
            voucher = trim(dw_2.getitemstring(r, "voucher_no"))

      if not isnull(voucher) then
            newsheet = xlapp.Application.ActiveWorkbook.Worksheets.add()
            xlsub = xlapp.Application.ActiveWorkbook.Worksheets[newsheet]
            xlapp.Application.ActiveWorkbook.Worksheets[newsheet].name(voucher)
      end if
      next

Any help would really, really be appreciated...

Thanks!

Paul
pemurrayAsked:
Who is Participating?
 
diasroshanCommented:
Hi,

just an example to enhace ur code to ADD TO LAST worksheet

OleObject newsheet
string voucher
Long i = 0
      For r = 1 to numrows
            voucher = trim(dw_2.getitemstring(r, "voucher_no"))

      if not isnull(voucher) then
            i++
            newsheet = xlapp.Application.ActiveWorkbook.Worksheets.add()
            xlsub = xlapp.Application.ActiveWorkbook.Worksheets[i]
            xlapp.Application.ActiveWorkbook.Worksheets[i].name(voucher)
      end if
      next

Try the above code... i havent tried it... we can work on it...

Cheers,
Rosh
0
 
pemurrayAuthor Commented:
One more thing, please...

I can add a sheet, but it adds it before another sheet.  I want it to add after.

I have tried a variety of things, none of which work...

newsheet = xlapp.Application.ActiveWorkbook.Worksheets.add(after)
newsheet = xlapp.Application.ActiveWorkbook.Worksheets.add([after])
newsheet = xlapp.Application.ActiveWorkbook.Worksheets.add("after")
newsheet = xlapp.Application.ActiveWorkbook.Worksheets.add(["after"])

Is there a cookbook somewhere that tells how to translate the examples for Excel to PowerBuilder?
0
 
diasroshanCommented:
Hi,

Replace,
any newsheet
with,
OleObject newsheet


Cheers,
Rosh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.