Greetings all.

I have an Excel add-in that opens a template in a new instance o
Excel, distinct from the worksheet from which the add-in runs. How ca
I get a message box to appear in the new instance instead of th
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") & "\Applicatio
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
-------------------------------------------------------------------------------

--
JeffK62
-----------------------------------------------------------------------
JeffK627's Profile: http://www.excelforum.com/member.php...fo&userid=2692
View this thread: http://www.excelforum.com/showthread.php?threadid=40148

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" wrote in
message ...

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" wrote in
message ...

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



wrote in message

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" wrote in message ...
"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