GetObject Example

Posted on 1999-01-20
Last Modified: 2012-08-14
In VB 5.0 the example of the GetObject funtion does not work properly. This is even after altering the syntax (missing comma's, different file reference). It is like it creates an instance of Excel for one second and then disappears. I think it becomes resident in memory, but after the Visible property is set to True, nothing happens. GetObject and other related functions are new to me, and I normally learn better from examples, but this one is stopping me. Please help.

Question by:cartti
  • 4
  • 4
LVL 70

Accepted Solution

Éric Moreau earned 70 total points
ID: 1469665
Send us your procedure and we will be able to help you much!

But you problem seems to be a scope problem. Do you declare the object variable (that contains your object return from GetObject) in the same event that you call GetObject?

If so, as soon as the event terminates, your object variable goes out of scope and you loose your instance.

Check the following code.

Public gobjExcel As Excel.Application

Public Sub OpenExcel()
' Getobject function called without the first argument
' returns a reference to an instance of the application.
' If the application isn't running, an error occurs.
On Error Resume Next
Set gobjExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
     Set gobjExcel = New Excel.Application
End If
End Sub

Author Comment

ID: 1469666
The example is found in the help file of Visual Basic. But here it is anyway (Remember to change "c:\vb4\MYTEST.XLS" to location of existing spreadsheet):

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName as String, ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd as Long,ByVal wMsg as Long, ByVal wParam as Long, ByVal lParam As Long) As Long

Sub GetExcel()
      Dim MyXL As Object      ' Variable to hold reference
      ' to Microsoft Excel.
      Dim ExcelWasNotRunning As Boolean      ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
      On Error Resume Next      ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
      Set MyXL = Getobject(, "Excel.Application")
      If Err.Number <> 0 Then ExcelWasNotRunning = True
      Err.Clear      ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,

' enter it into the Running Object table.

Set the object variable to reference the file you want to see.
      Set MyXL = Getobject("c:\vb4\MYTEST.XLS")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
      MyXL.Application.Visible = True
      MyXL.Parent.Windows(1).Visible = True
      ' Do manipulations of your
       ' file here.

' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
      If ExcelWasNotRunning = True Then
      End IF

      Set MyXL = Nothing      ' Release reference to the
      ' application and spreadsheet.
End Sub

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
      ' 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
LVL 70

Expert Comment

by:Éric Moreau
ID: 1469667
A year ago, I have tried to play with this sample and then I created my own code (which I already send you a part).

Here is a second part to open a file in Excel:
With gobjExcel
   .Workbooks.Open pstrFichierAOuvrir, , False, , strFichierPassword
   .Visible = True                
End With

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.


Author Comment

ID: 1469668
Thanks, but I want to have control over Excel in Visual Basic. I think the code you gave me belongs to Excel rather than VB 5.
LVL 70

Expert Comment

by:Éric Moreau
ID: 1469669
No. You have to paste this code in a VB project.

You have three ways to open an Automation server (like Excel) from VB: GetObject, CreateObject and New.

Once open with any method you will have the same control of your automation server.


Author Comment

ID: 1469670
I can't agree. For example Public gobjExcel As Excel.Application    is specific to VBA. That would not work in VB5.
LVL 70

Expert Comment

by:Éric Moreau
ID: 1469671
This line is actually written in a VB5 project.

What I forgot to mention is to go to the References dialog box (from the Project menu) and add "Microsoft Excel 8.0 Object Library".

Author Comment

ID: 1469672
That'll do!

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question