Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

604 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