Calling a VBA macro from Perl script

**nickmas** · 07-03-2013, 10:09 AM

Hi all,

I have an excel sheet (extension .xlsm) with mutliple sheets and a lot of functions/formula's to be calculated using the inputparameters in the first sheet. That's why I'm using a Perl script to update the parameters in the first sheet and afterwards (re)calculate all values in all other sheets.

To (re)calculate the Excel sheet, I created the following macro:

Please Login or Register  to view this content.

If I execute this macro manually in the excel sheet, the recalculation runs successfully.

However, I want to call this macro from my Perl script which has the following code:

Please Login or Register  to view this content.

The macro is excuted successfully, I see that the "BEGIN" and "END" is printed correctly in the right cells. These prints are in the macro, before and after the calculation function. However, the (re)calcalation doesn't seem to work as my functions/fomula's are not recalculated.

I hope somebody can help me?

**Norie** · 07-03-2013, 10:12 AM

Why don''t you use the Perl script to do what the macro does?

**nickmas** · 07-03-2013, 10:15 AM

Originally Posted by Norie

Why don''t you use the Perl script to do what the macro does?

Problem is I don't know how to do this in Perl...

**Norie** · 07-03-2013, 10:24 AM

Why are you using Perl?

Couldn't you do everything in VBA?

**nickmas** · 07-03-2013, 10:36 AM

Maybe .. you mean with a Visual Basic script?

Can you please help me how to ..
(1) open excel file in VB script?
(2) do a (re)calculate off the full excel workbook in the script?

thanks in advance!

**shg** · 07-03-2013, 11:02 AM

Please Login or Register  to view this content.

**Norie** · 07-03-2013, 11:06 AM

No I mean with VBA.

**nickmas** · 07-04-2013, 04:14 AM

Please Login or Register  to view this content.

I'm using the code above right now and this works except .. the calculate (which is still not done

)

**nickmas** · 07-09-2013, 10:30 AM

I'm currently using the code below:

test.vbs

Please Login or Register  to view this content.

However, the Prophet functions (add-ins) are not loaded correctly and that's why all formulas (which make use of this add-in) are not executed and the "CalculateFull" doesn't do anything. Please can anybody help me on how to open the excel with all add-ins?

If I open the excel manually (with the same user as the one which I use to execute the VBS script), it's no problem and the Prophet add-ins are loaded correctly. I also see there's an extra "Prophet" tab in Excel, like "Home", "Insert", "Page Layout", "Formulas", "Data",...