Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Failed to execute excel macro

  1. How-to
0
Votes
Undo
  1. Barry Cheung
    Barry Cheung
  2. PowerBuilder
  3. Wednesday, 6 January 2021 03:23 AM UTC

Hi,

I encounter following problem when execute excel macro in Powerbuilder.

Environment:
- Power Builder 2017 R3 on Windows 10
- Excel version is Excel for Office 365

Problem script:
try
   lo_myoleobject.application.run("ThisWorkbook.change_data_format")
catch (oleruntimeerror er)
   lo_myoleobject.disconnectobject()
  destroy lo_myoleobject
  return -1
end try

When execute above scripts, it return "Error calling external object function run at line 112 in function...."

The problem line is "lo_myoleobject.application.run("ThisWorkbook.change_data_format")"

However, there is no problem if use Excel 2003.

Please let me know what could be the issue?. Thanks.

 

Regards

Datawindow Saveas PDF
Function PrintGetPrinter causes a crash
Accepted Answer
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 7 January 2021 11:45 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi Barry,

First of all: In office 365 when you save an excel sheet with a macro as XLSX you'll see this message:

If you answer 'yes' and view your macros, your macro is still there, but ... if you then close excel and open the xlsx again, the macro will be GONE!

So what you have to do is save in XSLM format (macro enabled).

Then another thing you have to do, is go to "File > Options > Trust Center (left, below)" and click on the button "Trust Center Settings" (on the right, middle of the window):

Then you have to mark these 2 options that I've marked in yellow on the below image and click on Ok.
(Below the image I'll paste some code which works for me in PB2019.)

// test macro excel
oleobject lolexcel

lolexcel = create oleobject
try
	lolexcel.ConnectToNewObject( "excel.application" )
catch (oleruntimeError oleErr)
	Messagebox("Error opening Excel ", oleErr.GetMessage() )
end try
try
	// it HAS to be an XLSM sheet and you have to enable macros in the trust center
	lolexcel.workbooks.Open("c:\users\miguel.rsl\desktop\hola.xlsm") 
	lolexcel.DisplayAlerts = true
	lolexcel.visible = true
	lolexcel.application.run("hola.xlsm!test")
catch (oleruntimeerror oleErr1)
	Messagebox("Error opening Excel ", oleErr1.GetMessage() )
end try

lolexcel.quit()
destroy lolexcel
GarbageCollect()

 

Comment
Barry Cheung
  1. Barry Cheung
  2. Friday, 8 January 2021 02:33 AM UTC
Hi Miguel,



Thanks for your clear explanation. It works after I changed to use ConnectToNewObject method as following. The macro in 2003 Excel files (,xls) can be processed too.



li_result = lo_myoleobject.ConnectToNewObject( "excel.application" )

try

lo_myoleobject.workbooks.Open("C:\a.xls")

lo_myoleobject.application.run("ThisWorkbook.change_data_format")

catch (oleruntimeerror er)

lo_myoleobject.disconnectobject()

destroy lo_myoleobject

return -1

end try



Many thanks for your help.



Regards

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 8 January 2021 02:39 AM UTC
That’s great news, glad I could help
  1. Helpful
There are no comments made yet.
Responses (5)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 6 January 2021 09:04 AM UTC
  2. PowerBuilder
  3. # 1

Hi,

Try to see if the macro runs from the Excel 365 application, not from powerbuilder. If it does work, then save the spread sheet also in 365 format. Lots of things might have changed from excel 2003 to 365.

There's also a lot of added security to the later versions of office. Maybe you have to change some settings in "security center" and allowing to "run macros".

regards

Comment
Barry Cheung
  1. Barry Cheung
  2. Thursday, 7 January 2021 02:09 AM UTC
Thanks for your suggestion.



I have tried the macro in Excel application, the macro runs success without error.

I also tried to save the excel file to 365 format. No problem is Excel application but failed when invoke from PB via OLE
  1. Helpful
There are no comments made yet.
Gunnar Gisleberg
Gunnar Gisleberg Accepted Answer Pending Moderation
  1. Wednesday, 6 January 2021 09:27 AM UTC
  2. PowerBuilder
  3. # 2

How to secure Powerbuilder 2019 executables using code signing certificates?

 

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 6 January 2021 09:38 AM UTC
Hi Gunnar,

If this is a new question, please don't create it as the reply to another question, but create a new one by using the "pencil" icon at the top of this page in the blue menu bar.

:)

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 6 January 2021 15:25 PM UTC
  2. PowerBuilder
  3. # 3

Hi Barry;

  FWIW: My guess is that the Run () argument could be the issue. Especially, the change_data_format part between Excel 2003 and Excel 365

Regards ... Chris

Comment
Barry Cheung
  1. Barry Cheung
  2. Thursday, 7 January 2021 02:07 AM UTC
Yes, it failed at Run(). I have tried the macro in the Excel file, the macro runs success without error...
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 7 January 2021 02:32 AM UTC
  2. PowerBuilder
  3. # 4

I just recorded a simple macro in an excel 365 workbook and then created another macro that runs it. The syntax seems to need an exclamation mark "!" between the workbook name and the name of the macro.

Like this:

 

Application.Run "objetos.xlsx!TestMacro"

In your case something like:

lo_myoleobject.application.run("nameofExcelWorkbook.xlsx!change_data_format")

 

Hope it works :)

Edit: You HAVE to specify whichever extension your workbook has:

xlsx or xls or xlsm (=macro enabled workbook). I've tried without the extension and it doesn't work not even from excel itself.

 

Comment
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.