Word & Excel MVP code problem

[Al Uk]

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 Marcil]

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
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Al UK]

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

 

[Al uk]

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

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

 

[Jean-Guy Marcil]

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
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Al UK]

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.

 

[Jean-Guy Marcil]

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
(e-mail address removed)
Word MVP site: http://www.word.mvps.org