GetObject Example

Posted on 1999-01-20
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 70

Accepted Solution

Éric Moreau earned 280 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

770 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