1

Given that I have a UserForm embedded in one of the following:

  • an Excel workbook named c:\myBook.xslm
  • a Word document c:\myDocument.docm, or
  • a PowerPoint presentation named c:\myPresentation.ppm

What Automation properties / methods do I need to use in order to open and display the UserForm from an external script / host application / program?

For example, let's say I have the following JScript running under WSH:

var app = new ActiveXObject('Excel.Application');
app.Visible = true;
var book = app.Workbooks.Open('c:\myBook.xlsm');

// open UserForm here

How would I proceed to open the UserForm?

Note: I am looking for a solution that would work with an arbitrary document. This precludes manually (but not programmatically as part of the script) adding a Sub to show the UserForm, which can be called from the external script.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136

1 Answers1

0

An idea is to override the document open mehtods. At least for Microsoft Word and Excel:

' Word
Private Sub Document_Open()
  UserForm1.Show
End Sub

' Excel
Private Sub Workbook_Open()
  UserForm1.Show
End Sub

At word and excel document open, the dialog will be shown.

For Powerpoint its a bit more complicated:

How to auto execute a macro when opening a Powerpoint presentation?

Update

After additional information in the question this is not a solution anymore. A starting point how to create code an add it to a VBA project can be found here: https://stackoverflow.com/a/34838194/1306012

Also this website provides additional information: http://www.cpearson.com/excel/vbe.aspx

Bruno Bieri
  • 9,724
  • 11
  • 63
  • 92
  • In the `c:\myBook.xlsm` add a method `Workbook_Open()`. This method will be executed at opening the Workbook. Once your code executes the line `var book = app.Workbooks.Open('c:\myBook.xlsm');` the mothod should be called and therefore your userform being showed. – Bruno Bieri May 07 '18 at 12:41
  • Ah, I understand. After your question update my proposal is not applicable anymore. – Bruno Bieri May 07 '18 at 13:34
  • The only thing I can think of is to use the `Run` method but also for that you need a macro in the document to call which would show the UserForm. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-run-method-excel To me there is no way to directly call a UserForm from "outside" a VBA macro itself. – Bruno Bieri May 07 '18 at 13:47
  • Unless it's possible to create such a macro from the external program, and then call the created macro. – Zev Spitz May 07 '18 at 13:57
  • I would like to proof me wrong but to my knowledge the macros are saved in a binary format within the document. In case you know how to create this binary format yourself, modify the document, save it temporarly and then open it... Then I could work. By the way, how do you know how the name of the `UserForm` within a document if you don't know the document itself? – Bruno Bieri May 07 '18 at 14:02
  • I think it is possible to use the VBA Addin API to [add code modules](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/add-method-vba-add-in-object-model), and view the existing UserForms in a document, based on the [**VBComponent**](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/vbcomponent-object-vba-add-in-object-model)'s [**Type**](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/type-property-vba-add-in-object-model) property. – Zev Spitz May 08 '18 at 05:39
  • I found a starting point here: https://stackoverflow.com/a/34838194/1306012 In the comments of this answer there is also a reference to this page: http://www.cpearson.com/excel/vbe.aspx – Bruno Bieri May 09 '18 at 05:57