• 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


    Regards

  • 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?


    Hans

  • 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.


    HTH




    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
    Range("D:I").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=.Value


    End With
    End With
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy



    With appWord
    .Documents.Add
    .ActiveWindow.View.Type = wdPrintView
    With .Selection
    .TypeParagraph
    .TypeParagraph

  • 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)


    Thanks