• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • Last Modified:

GetObject Example

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.

Thanx
0
cartti
Asked:
cartti
  • 4
  • 4
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
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
0
 
carttiAuthor Commented:
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.
      DetectExcel

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
            MyXL.Application.Quit
      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
      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
0
 
Éric MoreauSenior .Net ConsultantCommented:
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

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
carttiAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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.


0
 
carttiAuthor Commented:
I can't agree. For example Public gobjExcel As Excel.Application    is specific to VBA. That would not work in VB5.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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".
0
 
carttiAuthor Commented:
That'll do!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now