MsgBox in new instance of Excel?

Greetings all.

I have an Excel add-in that opens a template in a new instance of Excel, distinct from the worksheet from which the add-in runs. How can I get a message box to appear in the new instance instead of the original one? I'm completely stymied here.

Here is the code that launches the template:



code:
--------------------------------------------------------------------------------
Public Sub OpenExcelTemplate()

'Create Excel Objects to display reports

On Error GoTo Error_Handler

gsTemplateFile = Environ("USERPROFILE") & "\Application Data\Microsoft\Templates\" & gsTemplateFile

'Open Excel file
Set objXL = GetObject("", "Excel.Application")
Set objWorkBooks = objXL.Workbooks
Set objWorkBook = objXL.Workbooks.Open(gsTemplateFile)
Set objWorkSheets = objWorkBook.Worksheets

With objXL
.Cursor = xlWait
.StatusBar = "Generating Reports. Please be patient..."
End With

Exit Sub
Error_Handler:
LogFile.WriteLine CStr(Now()) & ": Could not open Excel Template. Error: " & Err.Number & " " & Err.Description
Err.Clear

End Sub
--------------------------------------------------------------------------------

You also posted this same question on the MrExcel message board. See if my
response there is a solution you can use, at:
http://www.mrexcel.com/board2/viewtopic.php?t=166529




"JeffK627" <JeffK627.1uqlmp_1125673535.4544@excelforum-nospam.com> wrote in
message news:JeffK627.1uqlmp_1125673535.4544@excelforum-nospam.com...
>
> Greetings all.
>
> I have an Excel add-in that opens a template in a new instance of
> Excel, distinct from the worksheet from which the add-in runs. How can
> I get a message box to appear in the new instance instead of the
> original one? I'm completely stymied here.
>
> Here is the code that launches the template:
>
>
>
> code:
> --------------------------------------------------------------------------------
> Public Sub OpenExcelTemplate()
>
> 'Create Excel Objects to display reports
>
> On Error GoTo Error_Handler
>
> gsTemplateFile = Environ("USERPROFILE") & "\Application
> Data\Microsoft\Templates\" & gsTemplateFile
>
> 'Open Excel file
> Set objXL = GetObject("", "Excel.Application")
> Set objWorkBooks = objXL.Workbooks
> Set objWorkBook = objXL.Workbooks.Open(gsTemplateFile)
> Set objWorkSheets = objWorkBook.Worksheets
>
> With objXL
> Cursor = xlWait
> StatusBar = "Generating Reports. Please be patient..."
> End With
>
> Exit Sub
> Error_Handler:
> LogFile.WriteLine CStr(Now()) & ": Could not open Excel Template.
> Error: " & Err.Number & " " & Err.Description
> Err.Clear
>
> End Sub
> --------------------------------------------------------------------------------
>
>
> --
> JeffK627
> ------------------------------------------------------------------------
> JeffK627's Profile:
> http://www.excelforum.com/member.php...o&userid=26928
> View this thread: http://www.excelforum.com/showthread...hreadid=401484
>

Tom Urtis posted a link to his interesting solution. By comparison this is a
kludge of a workaround -

Option Explicit

Public Declare Function FindWindow32 Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Public Declare Function SetForegroundWindow Lib "user32" _
(ByVal hwnd As Long) As Long

Sub testW()
Dim hw1 As Long, hw2 As Long
Dim nWinState As Long
Dim xlApp1 As Excel.Application
Dim xlApp2 As Excel.Application

Set xlApp1 = Application
Set xlApp2 = CreateObject("Excel.Application")
DoEvents ' ?
xlApp2.Visible = True

hw1 = FindWindow32(vbNullString, xlApp1.Caption)
hw2 = FindWindow32(vbNullString, xlApp2.Caption)

'minimize our Instance but make it the front window
nWinState = xlApp1.WindowState
xlApp1.WindowState = xlMinimized
SetForegroundWindow hw1

MsgBox "Hello"

' restore as was
SetForegroundWindow hw2
xlApp1.WindowState = nWinState

'do whatever with xlApp2 (the new instance)

'stop
'close the new instance & release the object var'
xlApp2.Quit
Set xlApp2 = Nothing
Set xlApp1 = Nothing

End Sub

BTW, you say your addin creates a new instance, but in your code GetObject
gets whatever running instance of Excel by chance comes to hand.

Regards,
Peter T

"JeffK627" <JeffK627.1uqlmp_1125673535.4544@excelforum-nospam.com> wrote in
message news:JeffK627.1uqlmp_1125673535.4544@excelforum-nospam.com...
>
> Greetings all.
>
> I have an Excel add-in that opens a template in a new instance of
> Excel, distinct from the worksheet from which the add-in runs. How can
> I get a message box to appear in the new instance instead of the
> original one? I'm completely stymied here.
>
> Here is the code that launches the template:
>
>
>
> code:
> --------------------------------------------------------------------------
------
> Public Sub OpenExcelTemplate()
>
> 'Create Excel Objects to display reports
>
> On Error GoTo Error_Handler
>
> gsTemplateFile = Environ("USERPROFILE") & "\Application
> Data\Microsoft\Templates\" & gsTemplateFile
>
> 'Open Excel file
> Set objXL = GetObject("", "Excel.Application")
> Set objWorkBooks = objXL.Workbooks
> Set objWorkBook = objXL.Workbooks.Open(gsTemplateFile)
> Set objWorkSheets = objWorkBook.Worksheets
>
> With objXL
> Cursor = xlWait
> StatusBar = "Generating Reports. Please be patient..."
> End With
>
> Exit Sub
> Error_Handler:
> LogFile.WriteLine CStr(Now()) & ": Could not open Excel Template.
> Error: " & Err.Number & " " & Err.Description
> Err.Clear
>
> End Sub
> --------------------------------------------------------------------------
------
>
>
> --
> JeffK627
> ------------------------------------------------------------------------
> JeffK627's Profile:
http://www.excelforum.com/member.php...o&userid=26928
> View this thread: http://www.excelforum.com/showthread...hreadid=401484
>

"JeffK627"

1. Add a new Sub in a standard module in the "gsTemplateFile" workbook.
Title it "DisplayMsgBox"
2. Within this sub create your msgbox... MsgBox "Counting Sheep"
3. In your OpenExcelTemplate sub Call the new sub...
objXL Run "DisplayMsgBox"

Jim Cone
San Francisco, USA


<JeffK627.1uqlmp_1125673535.4544@excelforum-nospam.com>
wrote in message
news:JeffK627.1uqlmp_1125673535.4544@excelforum-nospam.com
Greetings all.
I have an Excel add-in that opens a template in a new instance of
Excel, distinct from the worksheet from which the add-in runs. How can
I get a message box to appear in the new instance instead of the
original one? I'm completely stymied here.

Here is the code that launches the template:
code:
--------------------------------------------------------------------------------
Public Sub OpenExcelTemplate()

'Create Excel Objects to display reports

On Error GoTo Error_Handler

gsTemplateFile = Environ("USERPROFILE") & "\Application
Data\Microsoft\Templates\" & gsTemplateFile

'Open Excel file
Set objXL = GetObject("", "Excel.Application")
Set objWorkBooks = objXL.Workbooks
Set objWorkBook = objXL.Workbooks.Open(gsTemplateFile)
Set objWorkSheets = objWorkBook.Worksheets

With objXL
Cursor = xlWait
StatusBar = "Generating Reports. Please be patient..."
End With

Exit Sub
Error_Handler:
LogFile.WriteLine CStr(Now()) & ": Could not open Excel Template.
Error: " & Err.Number & " " & Err.Description
Err.Clear

End Sub
--------------------------------------------------------------------------------


--
JeffK627
------------------------------------------------------------------------
JeffK627's Profile: http://www.excelforum.com/member.php...o&userid=26928
View this thread: http://www.excelforum.com/showthread...hreadid=401484

Correction... omitted the dot.
should be: objXL.Run "DisplayMsgBox"
Jim Cone



"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message news:ecBpUL$rFHA.3340@TK2MSFTNGP15.phx.gbl...
"JeffK627"
1. Add a new Sub in a standard module in the "gsTemplateFile" workbook.
Title it "DisplayMsgBox"
2. Within this sub create your msgbox... MsgBox "Counting Sheep"
3. In your OpenExcelTemplate sub Call the new sub...
objXL Run "DisplayMsgBox"
Jim Cone
San Francisco, USA