Opening Excel via code

  • Thread starter Thread starter Hennie
  • Start date Start date
H

Hennie

Good day,

I am trying to import data from Excel into Access via VB. I do experience a
problem to access an add-in to Excel. The add-in is a program to extract
data from a different source into Excel. The add-in is called NetOffice and
is part of a larger program called ProcessNet.



My main problem is when I open Excel via VB from Access the NetOffice add-in
does not load. I must first open Excel manually and check if it is included
in the menu or not. What I have noticed is when I open Excel via the
CreateObject function no workbook is loaded while doing it manually Book1 is
loaded and NetOffice is loaded into the menu.

Is there some way or some other instruction that would ensure the add-in is
loaded?



I use the following instruction.



Set objXL = CreateObject("Excel.Application")



Windows XP and Access 20002



Thank you for your time.



Hennie
 
Try adding this line after your createobject:
Set xlBook = xlApp.Workbooks.Add
That will open a new workbook.
Now, it appears you don't have much experience with using COM and the Excel
object model. You are going to have some problems.

For example, you may find that after you run the code to create and
manipulate the workbook, that you can no longer open an Excel file. This is
because there is still an instance of Excel running. You will not find it on
the Applications tab of task manager, but on the Process tab.

You have to be very exact using the Excel object model from Access. If your
object references are not fully qualified, Access gets confused and starts an
additional instance of Excel on it's own.

Here is an example of one I use to create a new spreadsheet:

'Set up the necessary Excel Objcts
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
'Set error trapping back on
On Error GoTo Build_XL_Report_Error
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add

--------------------------------------
Here is the code for DetectExcel:

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
 
Thank you for this information. I do have a rather extensive experience
with interlinking or cross formating from Access to Excel but this was one
instance that I could not find an answer. What you have referred to
regarding the open instance and the inability to do anything I have
experience with more frustration I would want to remember.
I will try this tomorrow and see if it will add the add-in or not. Will let
you know tomorrow what happened.

Once again Thank you,

Hennie
 
Supplementing Klatuu's answer:

You can explicitly load the add-in, e.g.

objXL.AddIns("name of addin").Installed = True

See Help on the Excel.Application.AddIn object and AddIns collection.
 
Thank you John for that information as it may be what I am looking for.
Will check on it tomorrow.

Klatuu, I tried your suggestion but it has the same reaction whether I set
my workbook and does not load the add-in.

I did find a work around for the problem by using the shell function and
tried to follow it with the Get function. When I run it there is an error,
which I cannot remember now, but have something to do that it can not run
the Get function after the Shell function. By stepping the code from there
the problem disappear and I can step through the whole code without a
problem.

I will add On Error Resume Next before the get function and see what
happens. If Johns' suggestion don't work the other alternative I see will be
be to check if Excel is running, if so, quit and run the shell function to
open Excel.

Will report on it tomorrow.

Thank you .

Hennie
 
I did use the function as suggested by John but still do have a problem.
The problem is not with the function but I would rather say with the Add-in
name as it does not recognize the name.

What I ended doing was to enter the code to check if Excel is running, if
yes, quit and use the shell function to open Excel. Then follow the normal
procedure of setting the Excel as application.

Once again thank you for the information provided.

Hennie
 
The problem is not with the function but I would rather say with the Add-in
name as it does not recognize the name.
Click to expand...

From Excel Help:

"Don’t confuse the add-in title, which appears in the Add-Ins dialog
box, with the add-in name, which is the file name of the add-in. You
must spell the add-in title exactly as it’s spelled in the Add-Ins
dialog box, but the capitalization doesn’t have to match."
 
Back
Top