programmatic access to vba from java script does not work

Question

Currently, we have a report which runs

User triggers the report
report is processed and saved in xml format
When the report is finished, user get the option to download the report
When user clicks on download, a web service is called.
1. this web service opens blank excel book
2. copies macro from a separate xml module (which resides in the server)
3. Macro processes finished xml report and show it in the sheets

This all works perfectly fine when using Excel 2002 (SP 3).

Our workstations are planned for upgrade from XP to Windows 7. With Windows 7, comes Excel 2010. I tried the whole step in the testing machine provided by infra team.

When testing, we found that macro from xml module was not getting copied in excel 2010. No errors and no warnings.

I did a bit of searching and found that "Trust access to the VBA project object model" needs to be ticked. Tried doing it but to no avail.

Let me know if anything is not clear or need more information.

Edit 1 This is how JS is opening the excel + adding workbook + adding module:

xlApp = new ActiveXObject("Excel.Application");
//Add a workbook
xlBook = xlApp.Workbooks.Add;

// This loads the modules into XL from the reporter_modules
var xlMod;
xlMod = xlBook.VBProject.VBComponents.Add(1);
xlMod.CodeModule.AddFromString(xmlModule.childNodes(i).text);

Since there is just .xls in Excel 2002, code does not mention the extension while opening excel anywhere. Do you know how can this be achieved?

Thanks, Meet

Just in case, For all the MS Product, after you change setting, it only activates after you close the application and activates on the next time. — Larry
– Larry, Commented Jan 8, 2013 at 8:55
@Larry, thanks for the comment. Did that already. Restarted the machine to make sure the check box is still ticked. — Meet
– Meet, Commented Jan 8, 2013 at 8:58
could it be that you have problems with accessing newly created files, as they are normally not in "full access" for everyone? maybe windows7 blocks the access from server. — Vogel612
– Vogel612, Commented Jan 8, 2013 at 10:02
Are you using .xlsx or .xslm file type? .xlsx won't allow macro modules. — Pynner
– Pynner, Commented Jan 8, 2013 at 13:58
@Pynner: Please see my edit in the question. Code does not mention the extension when opening the excel. Is there a way to open a new/blank excel workbook with extension? — Meet
– Meet, Commented Jan 9, 2013 at 4:57

Pynner · Accepted Answer · 2013-01-09 16:14:38Z

1

You can specify the file type on save. Which is in a different section of the code than what you have posted. excel will allow you to add the Vba modules to the .xlsx but will give a warning on save (which your java code is no doubt supressing). I don't know the java syntax but in Vba it is

ActiveWorkbook.SaveAs "C:\ron.xlsm", fileformat:=52

52 is a filetype constant that excel uses. You can see more detail @ http://www.rondebruin.nl/saveas.htm

answered Jan 9, 2013 at 16:14

Pynner

1,01710 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Meet Over a year ago

No good. Since the report is running in web server. I can't have JS save the excel sheet on client side. Infect, report when running in Office 2002 is also not saved on clients local (he has to save it manually).

Pynner Over a year ago

Hmm I think the issue is not related to the file format then. It might be worth trying just to see if that is the issue. I am now a bit more inclined to think it related to Windows 7 security. do you have a XP/Excel 2010 machine that you can try?

Meet Over a year ago

Tried in XP with excel 2010 but same result. Macro is not getting copied :(

Pynner Over a year ago

I'll throw out something else to try if possible, can you run the code on the server directly (IE client machine is the server?).

Meet Over a year ago

Well..they have not installed Office in the server..so can not try it.

Collectives™ on Stack Overflow

programmatic access to vba from java script does not work

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related