Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I was wondering f soemone could give me some advice on OLE Objects with VB/VBA

    I'm am just starting to learn OLE coding and creating Objects and such and I have come across a few issues I hope could be resolved.

    I am trying to create an interface that connects to Excel, modifies an existing worksheet in a already dreated workbook and then returns the results in the controls on the form.

    I have tried creating and setting up the object in the Load_Form event similar to below

    Dim exlApp As Word.Application
    Dim exlDoc As Word.Document

    Set exlApp = CreateObject("Excel.Application")

    Set exlDoc = exlApp.Documents.Open("C:\Foldername\Filename.xls")

    but then I don't know how to then access this object from other procedures (ie other events or subs)

    I hope this is relevant and any feedback or advice would be highly appreciated


  • Do you want to start a Word application from Excel, or the other way around. Your code mixes up Word and Excel. Can you have a look at your code and be more specific to what you really want to do?


  • This is a snippet of some code that I used to filter a worksheet and transfer the information to a word document. At the end of you module you would want to add set appword = nothing or if calling excel from word set xlapp = nothing.


    Sub PrintIndividual()

    Dim appWord As New Word.Application

    appWord.Visible = True 'make word visible
    Sheets("CustRet").Select 'select sheet to filter

    frmIndReports.Show Modeless
    With UserForm
    With cboBr
    Selection.AutoFilter Field:=1, Criteria1:=.Value

    End With
    End With

    With appWord
    .ActiveWindow.View.Type = wdPrintView
    With .Selection

  • Hans,

    Sorry bout the inaccurate post...

    I wast to control Word from Excel. I have no problems creating the object and then transferring data between applications however if I want to refer back to the object in another procedure I ahve to open it again after I have saved it in another procedure. Is ther any way of being able to access the same object (globally perhaps) without having to reopen it (ie keep it open for all prcedures)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!