Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Open OLE object with VBA

    I've linked a table that contains a field that is OLE data type. The field contains Excel documents. I'd like to create a command button on my form
    that will, when clicked, find and open the excel document that corresponds to
    the Product code that is active on my form. The only way I've figured out so far is to have the command button open a subform (or pop-up) that contains a control with the OLE object embedded. I can double click on that
    control and open the object.

    I was hoping to skip the subform and just open the object, but I can't find any useful info in the help files to get me started in the right direction.

    Thanks
    Mark
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you considered storing the UNC path to the document instead of the object? You can then use hyperlinks or shell commands to do exactly what you want (not to mention the benefits of saving disk space!)
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup. That's what I do, just store the path to the file in the database and use APIs to both set and open the document.

    You won't find much help in the help files on that though.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks for the input.

    Unfortunately, I can't get the owner of the table to make the change. There's about 3000 records, and he's not willing to save all of those OLE objects as files again.

    SO... back to my original question. Is it possible to open an OLE object stored in a field with VBA?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think OLE objects have a .SourceDoc property which can give you the filename... So if you can grab this you should be good to go
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think he means they're embedded, not linked to existing files. I'd hate to think how big the data-file is.

    And yes it's possible using Me.<OLEObjectName>.Action = acOLEActivate
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    heh... yes, it's a monster. The ugliest table I've ever seen.

    Let me give a little more background, because this is more related to my actual job and not the "application development" piece of my job.

    When I come across a new product in production planning application, I rarely
    know what the sales forecast is. In the process of requesting a new product,
    the salesmonkey has to put a forecast in a Excel worksheet that is then embedded into someone else's database.

    I can link that huge table into my database, and manually find the row for that product and double click on the field that contains the "Microsoft Excel Worksheet" object. However, that's too much work.

    I really would like to put a command button on my form that will use ADO to find the right record, and then do something like:

    rs!OleField.Activate

    Of course, that doesn't work...
    Inspiration Through Fermentation

  8. #8
    Join Date
    Aug 2009
    Posts
    1

    This is how!

    There are two ways to do this.

    Easiest, write a macro to open a form containing the OLE object.

    Use the Selectobject (the form) and the GotoControl (select the object on the form), then Sendkeys ^{ENTER} (Control+Enter).

    This opens the object.

    In VB, select the control on the form which contains the OLE Object

    Change the Auto Activate property to 1

    This will open the object on the getfocus event, or in other words, as soon as the form is opened.

    I prefer the first method as it allows me to select out of a number of Objects before using a command button to run the Make Table Query etc to create the datafile which my OLE embedded Word Doc is linked to.

    Hope that helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •