Word & Excel MVP code problem

http://www.word.mvps.org/faqs/interdev/ControlXLFromWord.h
tm

I have been attempting to use this code on two PCs using
Office 2000 and a friend has tried on Office XP, but in
neither case was the code able to open Excel where it was
not already running, just open the named file when it was
already running. The code ran with no errors reporting,
but nothing actually happened whenExcel was not running.
The code was copied and pasted, with only the file
pathname being changed. I managed to select the Microsoft
Excel Object 9 ( 10 in XP) Library - although this option
was not present for selection in the references until the
code had run and failed at least once.

Is there a problem with this code?

Jean-Guy
Mon Jul 05 21:08:19 CDT 2004

Bonjour,

Dans son message, < Al Uk > écrivait :
In this message, < Al Uk > wrote:

|| http://www.word.mvps.org/faqs/interdev/ControlXLFromWord.h
|| tm
||
|| I have been attempting to use this code on two PCs using
|| Office 2000 and a friend has tried on Office XP, but in
|| neither case was the code able to open Excel where it was
|| not already running, just open the named file when it was
|| already running. The code ran with no errors reporting,
|| but nothing actually happened whenExcel was not running.
|| The code was copied and pasted, with only the file
|| pathname being changed. I managed to select the Microsoft
|| Excel Object 9 ( 10 in XP) Library - although this option
|| was not present for selection in the references until the
|| code had run and failed at least once.
||
|| Is there a problem with this code?

No, there shouldn't be.

Have you debugged your code to see what was happening?
Place the cursor anywhere inside your code and hit F8 to run the code line
by line, You will have to hit F8 to execute each line. Watch what is
happening with Word and Excel and report back with your findings and the
associated code.

I am puzzled as to why you were not able to set the Excel library reference
before running the code. If Excel is installed , the library should be in
the list. If it isn't, then I believe there is something wrong with your
set-up.
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Al
Tue Jul 06 01:50:22 CDT 2004

If ExcelWasNotRunning Then
oXL.Quit
End If

This part of the code was closing Excel straight back=20
down again- removing it seems to have cured the problem

>-----Original Message-----
>Bonjour,
>
>Dans son message, < Al Uk > =E9crivait :
>In this message, < Al Uk > wrote:
>
>||=20
http://www.word.mvps.org/faqs/interdev/ControlXLFromWord.h
>|| tm
>||
>|| I have been attempting to use this code on two PCs=20
using
>|| Office 2000 and a friend has tried on Office XP, but=20
in
>|| neither case was the code able to open Excel where it=20
was
>|| not already running, just open the named file when it=20
was
>|| already running. The code ran with no errors=20
reporting,
>|| but nothing actually happened whenExcel was not=20
running.
>|| The code was copied and pasted, with only the file
>|| pathname being changed. I managed to select the=20
Microsoft
>|| Excel Object 9 ( 10 in XP) Library - although this=20
option
>|| was not present for selection in the references until=20
the
>|| code had run and failed at least once.
>||
>|| Is there a problem with this code?
>
>No, there shouldn't be.
>
>Have you debugged your code to see what was happening?
>Place the cursor anywhere inside your code and hit F8 to=20
run the code line
>by line, You will have to hit F8 to execute each line.=20
Watch what is
>happening with Word and Excel and report back with your=20
findings and the
>associated code.
>
> I am puzzled as to why you were not able to set the=20
Excel library reference
>before running the code. If Excel is installed , the=20
library should be in
>the list. If it isn't, then I believe there is something=20
wrong with your
>set-up.
>--=20
>Salut!
>_______________________________________
>Jean-Guy Marcil - Word MVP
>jmarcilREMOVE@CAPSsympatico.caTHISTOO
>Word MVP site: http://www.word.mvps.org
>
>
>
>.
>

Al
Tue Jul 06 07:22:35 CDT 2004

Obviously I woke up and realised my mistake...wood - trees=20
HELLO!

Problem now is that the spreadsheet I am loading utilizes=20
the Analysis Tool Pack Add-in to use the EDATE function -=20
this does not appear to load when you get the excel object?
all I get are #NAME? errors

>-----Original Message-----
>If ExcelWasNotRunning Then
> oXL.Quit
>End If
>
>This part of the code was closing Excel straight back=20
>down again- removing it seems to have cured the problem
>
>>-----Original Message-----
>>Bonjour,
>>
>>Dans son message, < Al Uk > =E9crivait :
>>In this message, < Al Uk > wrote:
>>
>>||=20
>http://www.word.mvps.org/faqs/interdev/ControlXLFromWord.h
>>|| tm
>>||
>>|| I have been attempting to use this code on two PCs=20
>using
>>|| Office 2000 and a friend has tried on Office XP, but=20
>in
>>|| neither case was the code able to open Excel where it=20
>was
>>|| not already running, just open the named file when it=20
>was
>>|| already running. The code ran with no errors=20
>reporting,
>>|| but nothing actually happened whenExcel was not=20
>running.
>>|| The code was copied and pasted, with only the file
>>|| pathname being changed. I managed to select the=20
>Microsoft
>>|| Excel Object 9 ( 10 in XP) Library - although this=20
>option
>>|| was not present for selection in the references until=20
>the
>>|| code had run and failed at least once.
>>||
>>|| Is there a problem with this code?
>>
>>No, there shouldn't be.
>>
>>Have you debugged your code to see what was happening?
>>Place the cursor anywhere inside your code and hit F8 to=20
>run the code line
>>by line, You will have to hit F8 to execute each line.=20
>Watch what is
>>happening with Word and Excel and report back with your=20
>findings and the
>>associated code.
>>
>> I am puzzled as to why you were not able to set the=20
>Excel library reference
>>before running the code. If Excel is installed , the=20
>library should be in
>>the list. If it isn't, then I believe there is something=20
>wrong with your
>>set-up.
>>--=20
>>Salut!
>>_______________________________________
>>Jean-Guy Marcil - Word MVP
>>jmarcilREMOVE@CAPSsympatico.caTHISTOO
>>Word MVP site: http://www.word.mvps.org
>>
>>
>>
>>.
>>
>.
>

Jean-Guy
Tue Jul 06 11:07:22 CDT 2004

Bonjour,

Dans son message, < Al UK > écrivait :
In this message, < Al UK > wrote:

| If ExcelWasNotRunning Then
| oXL.Quit
| End If
|
| This part of the code was closing Excel straight back
| down again- removing it seems to have cured the problem
|

The key word here is "seems"!

The only way this code would execute is if there is an error thrown when
opening Excel. If you remove this code, you still get an error when opening
Excel.

Debug again and carefully see where/how/why the code below this line throws
an error:
'_______________________________________
On Error GoTo Err_Handler
'_______________________________________

Also, you wrote that the code

| If ExcelWasNotRunning Then
| oXL.Quit
| End If

was being executed. If it was, how come you did not get the message box that
precedes?
(In your original post you wrote:

<quote>
already running. The code ran with no errors reporting,
but nothing actually happened whenExcel was not running.
<endquote> )

This means that no message box popped up, but if the above code was
executed, a message box had to pop up. See the original code that you pasted
directly from the MVP site:
'_______________________________________
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If
'_______________________________________

See? Because of the Exit Sub statement, the only way the Quit statement
would run is if there was an error, in which case a message box pops up. How
come you never got that message box?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Al
Tue Jul 06 11:33:46 CDT 2004

Ok, I didn't make myself clear- I had not got to the part=20
of putting any other code in, therefore it went through=20
the code and shut itself straight down, no error.

On Excel addins it seems these do not load when you start=20
Excel with VBA- which is a bit of a "nuisance". I solved=20
the problem by making the addins required in Excel re-
install themselves on an autoOpen macro in the=20
spreadsheet, using code:

AddIns("Analysis Toolpak").Installed =3D False
AddIns("Analysis Toolpak - VBA").Installed =3D False
AddIns("Analysis Toolpak").Installed =3D True
AddIns("Analysis Toolpak - VBA").Installed =3D True

as I could not find a way to call them from Word. Seems an=20
awfully complicated way to get the functionality you=20
already had..lol Now my EDATE functions are correctly=20
calculated.

>-----Original Message-----
>Bonjour,
>
>Dans son message, < Al UK > =E9crivait :
>In this message, < Al UK > wrote:
>
>| If ExcelWasNotRunning Then
>| oXL.Quit
>| End If
>|
>| This part of the code was closing Excel straight back
>| down again- removing it seems to have cured the problem
>|
>
>The key word here is "seems"!
>
>The only way this code would execute is if there is an=20
error thrown when
>opening Excel. If you remove this code, you still get an=20
error when opening
>Excel.
>
>Debug again and carefully see where/how/why the code=20
below this line throws
>an error:
>'_______________________________________
>On Error GoTo Err_Handler
>'_______________________________________
>
>Also, you wrote that the code
>
>| If ExcelWasNotRunning Then
>| oXL.Quit
>| End If
>
>was being executed. If it was, how come you did not get=20
the message box that
>precedes?
>(In your original post you wrote:
>
><quote>
>already running. The code ran with no errors reporting,
>but nothing actually happened whenExcel was not running.
><endquote> )
>
>This means that no message box popped up, but if the=20
above code was
>executed, a message box had to pop up. See the original=20
code that you pasted
>directly from the MVP site:
>'_______________________________________
>Exit Sub
>
>Err_Handler:
> MsgBox WorkbookToWorkOn & " caused a problem. " &=20
Err.Description,
>vbCritical, _
> "Error: " & Err.Number
> If ExcelWasNotRunning Then
> oXL.Quit
> End If
>'_______________________________________
>
>See? Because of the Exit Sub statement, the only way the=20
Quit statement
>would run is if there was an error, in which case a=20
message box pops up. How
>come you never got that message box?
>
>--=20
>Salut!
>_______________________________________
>Jean-Guy Marcil - Word MVP
>jmarcilREMOVE@CAPSsympatico.caTHISTOO
>Word MVP site: http://www.word.mvps.org
>
>
>
>.
>

Jean-Guy
Tue Jul 06 13:58:24 CDT 2004

Bonjour,

Dans son message, < Al UK > écrivait :
In this message, < Al UK > wrote:

| Ok, I didn't make myself clear- I had not got to the part
| of putting any other code in, therefore it went through
| the code and shut itself straight down, no error.
|
| On Excel addins it seems these do not load when you start
| Excel with VBA- which is a bit of a "nuisance". I solved
| the problem by making the addins required in Excel re-
| install themselves on an autoOpen macro in the
| spreadsheet, using code:
|
| AddIns("Analysis Toolpak").Installed = False
| AddIns("Analysis Toolpak - VBA").Installed = False
| AddIns("Analysis Toolpak").Installed = True
| AddIns("Analysis Toolpak - VBA").Installed = True
|
| as I could not find a way to call them from Word. Seems an
| awfully complicated way to get the functionality you
| already had..lol Now my EDATE functions are correctly
| calculated.
|

Did you try:

oXL.AddIns("Analysis Toolpak").Installed = False
etc.

after creating the Excel object in your Word code?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org