1. Konrad Kaltenbach
  2. PowerBuilder
  3. Monday, 29 March 2021 08:57 AM UTC

Dear Experts,

I want to call the VBA Excel functions with optional parameters like Range.PasteSpecial(...) for example in PowerScript functions.

I tried the following:

OLEObject lole_WS_From // Excel worksheet
OLEObject lole_WS_To    // Excel worksheet

(...)

 lole_WS_From.Rows("1:3").Copy // works

 lole_WS_To.Rows("1:3").PasteSpecial(-4163, -4142, False, False) // results in
                                                                                              // "Error calling external object function pastespecial"

Is it possible to call VBA Excel functions with optional parameters?

If it's possible: How is the syntax?

 

Thanks in advance for all trials to help me!

Kind regards,
Konrad Kaltenbach

 

Accepted Answer
Mark Goldsmith Accepted Answer Pending Moderation
  1. Monday, 29 March 2021 15:34 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Konrad,

The issue isn't the passing of parameters as you do have the correct syntax and your range expression is fine as well. It may be how you're accessing/ selecting the second OLE object and without seeing the rest of your code (how both objects are connected to etc.) it's hard to tell. lole_WS_To...what is that used for? Is it to connect to another workbook? Are you using ConnectToObject? ConnectToNewObject?

Maybe I'm misunderstanding what you're trying to accomplish but it could be how you're trying to utilize/ access lole_WS_To.  You don't need to create another OleObject, you simply need to change the focus of the current workbook and sheet...using the same OleObject...to the new workbook and sheet.

So applying that to your code:

myoleobject.Rows("1:3").Copy
myoleobject.Windows("Book2").Activate
myoleobject.Sheets("Sheet2").Select
myoleobject.Rows("1:3").PasteSpecial(-4163, -4142, False, False)

Try the above changes and see if that works but it may well depend on the answers to the questions in the first paragraph.

HTH...regards,

Mark

Comment
  1. Konrad Kaltenbach
  2. Wednesday, 31 March 2021 14:32 PM UTC


Dear Mark,







thanks a lot for your elaborate and comprehensive answers and your additional thoughts!



On your question: I wanted Excel stay "passive" and hidden until the table is filled. But probably no problems will appear when the window is activated as you showed me.



Regarding your thoughts:



You're right: I didn't publish the complete code of the function where PasteSpecial is called but only an extract which I assumed to be complete with respect to the OLE objects and your queries.



"myoleobject1.WorkBooks[2].WorkSheets[1].Rows("1:3").PasteSpecial(-4163, -4142, False, False)" I will try next week. Thanks! But, yes, probably activating the window will not cause any problems.



Grateful regards,



Konrad
  1. Helpful
  1. Konrad Kaltenbach
  2. Tuesday, 6 April 2021 10:14 AM UTC
Hi Mark,

following your suggestion I tried the following:

xlsInstanz.Application.Workbooks[li_WBk].Worksheets[li_WS].Rows("1:3").PasteSpecial(-4163, -4142, false, false )

(cf. my source code posted Tuesday, 30 March 2021 09:05 AM UTC and the comment). The error still occurred (as long as the window is not activated before).
  1. Helpful
  1. Konrad Kaltenbach
  2. Tuesday, 5 April 2022 06:41 AM UTC
ATTENTION! Using ConnectToObject is apparently necessary, too.

A workbook opened with "xlsInstanz.Application.Workbooks.Open(ls_file)"

after having started Excel with

"li_err = lole_Excel.ConnectToNewObject( "excel.application" )"

instead can apparently not be manipulated programmatically (with function calls like

"lole_WB.Worksheets(idx).Name = ls_ws_from" or

"lole_WS_To.Rows("1:3").PasteSpecial(-4163, -4142, False, False)" for example).
  1. Helpful
There are no comments made yet.
Ashutosh Varshney Accepted Answer Pending Moderation
  1. Monday, 29 March 2021 15:20 PM UTC
  2. PowerBuilder
  3. # 1

It's been a while since I worked with excel ole. I would suggest you to look at your range expression. PasteSpecial needs a valid range and probably Rows("1:3") is not valid. I found it helpful to test my syntax first in an excel macro.

 

Comment
  1. Konrad Kaltenbach
  2. Tuesday, 30 March 2021 08:43 AM UTC
Thanks for your comment! And sorry for the delay.



But "Rows("1:3")" works quite well.
  1. Helpful
There are no comments made yet.
Konrad Kaltenbach Accepted Answer Pending Moderation
  1. Tuesday, 30 March 2021 09:05 AM UTC
  2. PowerBuilder
  3. # 2

Dear Sir or Madam,

here is the complete source code:

Integer        li_err, li_WBk, li_WS_to, idx
String        ls_err
string        ls_file, ls_wb, ls_ws_to, ls_ws_from
OLEObject     xlsInstanz
OLEObject     lole_WB      //  
OLEObject     lole_Win     // Excel-Window
OLEObject     lole_WS_To   // Target sheet in lole_WB
OLEObject     lole_WS_From // Source sheet in lole_WB

ls_wb = "Name of an existing workbook"
ls_ws_from = "Name of source sheet"
ls_ws_to   = "Name of target sheet"
ls_file    = "C:\..." // complete path to workbook
SetNULL( lole_WS_To )
//
// Open EXCEL file
IF IsValid(xlsInstanz) THEN DESTROY xlsInstanz
xlsInstanz = CREATE OLEObject

TRY // Excel-Datei anzapfen
    li_err = xlsInstanz.connecttoobject( ls_file )
CATCH (OLEruntimeerror er2)
    DESTROY xlsInstanz
    ls_err += "Description: ~n" + er2.Description + " " +  er2.getMessage()  
END TRY
IF li_err <> 0 Or ls_err <> "" THEN GOTO ErrorHandler
//
// Identifying source and target sheet;
// adding target sheet if necessary.
li_WBk = xlsInstanz.Application.Workbooks.count // workbook, opened by this function
lole_WB = xlsInstanz.Application.Workbooks(ls_wb)
SetNull(lole_WS_To); lb_WS_neu = False  
SetNull(lole_WS_From)

For idx = 1 To  lole_WB.Worksheets.Count
    If lole_WB.Worksheets(idx).Name = ls_WS_to Then
        li_WS_to = idx
        lole_WS_To = lole_WB.Worksheets(idx)
    ElseIf lole_WB.Worksheets(idx).Name = ls_ws_from Then
        lole_WS_From = lole_WB.Worksheets(idx)
    End If
Next // sheet
    
If IsNull( lole_WS_To ) Then // add worksheet "Name of target sheet"
    lole_WB.worksheets(1).Activate   
    lole_WB.worksheets.Add( ) // sheet is added in front of active sheet
    li_WS_to = 1 // index of the new sheet, the target sheet
    lole_WS_To = lole_WB.Worksheets(li_WS_to)  
       lole_WS_To.Name = ls_WS_to
    lb_WS_neu = True
End If
//
//
Try // to copy rows 1 to 3 from source sheet to target sheet...

    lole_WS_From.Rows("1:3").Copy // works
    lole_WS_To.Rows("1:3").PasteSpecial(-4163, -4142, False, False) // here the error occurs:
    // "Error calling external object function pastespecial"


CATCH (OLEruntimeerror erKopfCopy)
    messagebox("", erkopfcopy.getmessage())
End Try

Comment
  1. Konrad Kaltenbach
  2. Tuesday, 6 April 2021 07:40 AM UTC
The error disappears if the the Excel window is activated (lole_win.Activate) between

lole_WS_From.Rows("1:3").Copy and lole_WS_To.Rows("1:3").PasteSpecial(-4163, -4142, False, False)

(cf. accepted answer by M. Goldsmith).

Without window activation the following three trials didn't work, too:

xlsInstanz.Application.Workbooks.Item(ls_wb).Worksheets.Item(ls_WS_to).Rows("1:3").PasteSpecial(-4163, -4142, false, false )

xlsInstanz.Application.Workbooks[li_WBk].Worksheets[li_WS].Rows("1:3").PasteSpecial(-4163, -4142, false, false )

xlsInstanz.Application.Workbooks(li_WBk).Worksheets(li_WS).Rows("1:3").PasteSpecial(-4163, -4142, false, false )

  1. Helpful
  1. Mark Goldsmith
  2. Tuesday, 6 April 2021 11:52 AM UTC


Hi Konrad...glad to hear you got this to work, even if only by activating it first. That being said, the syntax I provided which matches your second trial above does work. The only thing I can think of is that maybe your elements (for Workbooks and Worksheets) are off/ incorrect. From what I can see in the code above in your trials, the second two trials are using li_WS but in the first trial you are using ls_WS_to and in your original code posted March 30 you were using li_WS_to as the destination worksheet and so maybe that is where it is failing.



Again, glad to hear you got it working and thanks for the update.



Regards,

Mark
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.