How calling VBA Excel functions with optional arguments?

Resolved How calling VBA Excel functions with optional arguments?

How-to

Votes

Undo

Konrad Kaltenbach
PowerBuilder
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

Monday, 29 March 2021 15:34 PM UTC
PowerBuilder
# 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

Load more comments

Konrad Kaltenbach
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

Helpful 0

Konrad Kaltenbach
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).

Helpful 0

Konrad Kaltenbach
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).

Helpful 0

There are no comments made yet.

Responses (3)

Konrad Kaltenbach Accepted Answer Pending Moderation

Tuesday, 30 March 2021 09:05 AM UTC
PowerBuilder
# 1

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

Konrad Kaltenbach
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 )

Helpful 0

Mark Goldsmith
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

Helpful 0

There are no comments made yet.

Ashutosh Varshney Accepted Answer Pending Moderation

Monday, 29 March 2021 15:20 PM UTC
PowerBuilder
# 2

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

Konrad Kaltenbach
Tuesday, 30 March 2021 08:43 AM UTC

Thanks for your comment! And sorry for the delay.

But "Rows("1:3")" works quite well.

Helpful 0

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.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved How calling VBA Excel functions with optional arguments?

Find Questions by Tag