MsgBox - How to display In Other Instance of Excel

[Alan]

Hi All,

I am using a separate instance of excel to display an export from an
excel application in a different instance of excel (the original
instance being driven entirely by modal userforms which I don't want
to interrupt).

One export is a data integrity check where it exports, does some
checks, and then displays the lines with potential issues for the user
to investigate back to hard copy documents.

I would like to display a MsgBox in that instance of excel, using code
in my original instance, telling the user which specific test was
found to have potential data issues (makes it easier for the user to
target their investigation / checking).

However, I cannot work out how to make it display a MsgBox
in the other instance of excel.

Any suggestions?

Thanks,

Alan.


PS: I have looked through the excel object model for where a
MsgBox sits, but I cannot seem to see where it fits into the
hierarchy. I was hoping to be able to reference it something
like this:

+-+-+-+-+-+-+-+-+-+-+

This Instance Implicit:

MsgBox "Hello"

+-+-+-+-+-+-+-+-+-+-+

This Instance Explicit:

Thisworkbook.Parent.MsgBox "Hello"

+-+-+-+-+-+-+-+-+-+-+

Other Instance (objExcel being the new instance of excel):

objExcel.MsgBox "Hello"

+-+-+-+-+-+-+-+-+-+-+

 

[Tom Ogilvy]

have the instance in control run a macro in the other instance that displays
the msgbox

oxlApp.Application.Run "Tempbook.xls!Macro1"

 

[Alan]

have the instance in control run a macro in the other instance that
displays the msgbox

oxlApp.Application.Run "Tempbook.xls!Macro1"

Hi Tom,

Thanks for your quick reply.

I guess that implicitly means that I have to programmatically create
the macro in the workbook in the new instance, since that workbook was
created from scratch by my code.

I found the following post by the anonymous 's':

http://groups.google.co.nz/groups?hl=en&lr=&ie=UTF-8&[email protected]

which I amended slightly to try to do what I need (see below bottom).

The code errors out with an error 438 (object does not support this
property or method) on the line between my commented asterixes (****).

If I remove that line, it bombs out on the next line with the same
error.

I added that first line guessing that I needed to add a module first,
but obviously the issue is not (just) that?

Are you able to point me in the right direction?

Thanks,

Alan.


+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+

' Note: This code simplified to avoid excess post length'
' Only change was to reduce the MsgBox text to "Hello"


Sub Create_Code_In_Workbook(objexcel As Application, _
FieldNumberBlank As Integer, _
FieldNumberNonBlank As Integer, _
TotalRows As Integer)

' ****

objexcel.VBProject.VBcomponets.Add (vbext_ct_StdModule)

' ****

Set MyProject = objexcel.VBProject.VBcomponets("Module1").Codemodule

yourcode = "Sub Display_MsgBox" & vbCrLf
yourcode = yourcode + "Response = MsgBox(""Hello"")" & vbCrLf
yourcode = yourcode + "end sub"

With MyProject
.insertlines 3, yourcode
End With

End Sub

+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+

 

[Tom Ogilvy]

you mispelled component same as in the posting you cited.

objExcel should not be application, but a workbook.

this worked for me in a single instance:

Sub Create_Code_In_Workbook(objBk As Workbook, _
FieldNumberBlank As Integer, _
FieldNumberNonBlank As Integer, _
TotalRows As Integer)
Dim modl As Object

' ****

Set modl = objBk.VBProject.VBComponents.Add(vbext_ct_StdModule)

' ****

Set myproject = modl.CodeModule

yourcode = "Sub Display_MsgBox" & vbCrLf
yourcode = yourcode + "Response = MsgBox(""Hello"")" & vbCrLf
yourcode = yourcode + "end sub"

With myproject
.insertlines 3, yourcode
End With

End Sub

Sub Tester2()
Create_Code_In_Workbook ThisWorkbook, 1, 2, 3
Application.Run ThisWorkbook.Name & "!Display_MsgBox"
End Sub

 

[Alan]

you mispelled component same as in the posting you cited.

Serves me right I suppose!

objExcel should not be application, but a workbook.

this worked for me in a single instance:

{Snipped code}

Hi Tom.

Apologies for being slow, but I still cannot get it to work.

I have simplified everything to the code below, and used a separate
object name at each step in order to confirm exactly where it goes
wrong:

It stops on the lie where I try to add a Standard Module to the
VBComponent (spelt correctly now I think!).

I am using Excel 2000 SP3 running under Win2000 Pro - all fully
patched and updated.

Thanks again,

Alan.


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Sub Test()

Dim objExcel As Application
Dim objExcelWorkbook As Workbook
Dim MyProject As Object
Dim MyComponents As Object
Dim MyStdModule As Object


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objExcelWorkbook = objExcel.Workbooks.Add


Set MyProject = objExcelWorkbook.VBProject
Set MyComponents = MyProject.VBComponents


' **** Bombs on the following line ****

Set MyStdModule = MyComponents.Add(vbext_ct_StdModule)

' **** Bombs on the line above ****


End Sub

 

[Tom Ogilvy]

I see you have declared your variables as Object which would be appropriate
if you are using late binding (no reference to the visual basic
extensibility library). However, if that is the case

vbext_ct_StdModule

is an uninitialized variant variable with value 0. where it should have a
value of 1

? vbext_ct_StdModule
1


Zero would not be a legal argument to the line of code. Hard code a 1
(one).

 

[Alan]

I see you have declared your variables as Object which would be
appropriate if you are using late binding (no reference to the
visual basic extensibility library). However, if that is the case

vbext_ct_StdModule

is an uninitialized variant variable with value 0. where it should
have a value of 1

? vbext_ct_StdModule
1


Zero would not be a legal argument to the line of code.
Hard code 1 (one).

Hi Tom,

You were, of course, totally correct.

How might I have found that out for myself though? I could find
nothing in the VBA help that would indicate that the constant needed
to be 'initialised' in some way different from using, for example,
vbCRLF in a string.

How did you know?

Thanks,

Alan.

 

[Tom Ogilvy]

The constant is defined in the extensibility library - if you don't have a
reference to the extensibility library then it is undefined.

Using

Option Explicit

at the top of your module would have alerted you that the constant was
undefined. then you probably would have reasoned out what the cause was.

 

[Alan]

The constant is defined in the extensibility library - if you don't
have a reference to the extensibility library then it is undefined.

Using

Option Explicit

at the top of your module would have alerted you that the constant was
undefined. then you probably would have reasoned out what the cause
was.

Thanks Tom, I really appreciate your help on this.

Alan.