Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Melton Mowbray, UK
    Thanked 0 Times in 0 Posts

    An Excel Macro to control actions in Word? (Office 97)

    Using an Excel macro I would like to gather data from various Excel files
    then open a specific Word file, copy some data from the Word file and paste it
    in the Excel file. The Excel data and the Word data would provide a solution
    which I would like to paste back into the Word file.

    No problems manipulating the Excel data.
    I have come across:-

    Dim taskID As Variant
    taskID = Shell("WINWORD.EXE C:importantdata.doc", 1)

    So can open the appropriate Word file from Excel but I have no idea how to manipulate
    the Word file inside an Excel macro - is it possible even?

    Suggestions on where to look for help on this welcome!
    Or confirmation that it isn't possible using Excel macros.

    Tony Beckett

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: An Excel Macro to control actions in Word? (Office 97)

    To manipulate a Word document from another application, use Automation. This means that you define a Word application object in your Excel code, and then use this object to execute Word VBA. If you're doing this for the first time, it's a bit intimidating, but it's not really that difficult.

    I would advise you to start with recording or writing a macro that does what you want in Word itself. It's much easier to test and debug code in its own application. when you've got it right, it's failry easy to transfer the code to Excel.

    Note: in the beginning, you're bound to make mistakes. I know I did! Make copies of your files, save often and don't keep other files and applications open while experimenting. That way, you minimize damage when you crash.

    Now here is what you do in Excel:
    1. <LI>In the Visual Basic Editor, make sure that you're in the target spreadsheet.
      <LI>Select Tools/References...
      <LI>Look for the Microsoft Word 8.0 Object Library in the list and check the box to the left of it.
      <LI>Click OK. You can now write Word code in Excel.
      <LI>You need a Word object. I'll show you the simplest way now.

      Dim objWordApp As New Word.Application

      This instruction will start a new invisible instance of Word.
      <LI>To execute a Word VBA instruction, prepend the Word object to it.
      For instance, if you want to open an existing document:

      Dim objWordDoc As Word.Document
      Set objWordDoc = objWordApp.Documents.Open("filename")

      To paste something at the current location:


      <LI>It's important to close and release objects when you're done with them:

      objWordDoc.Close SaveChanges:=True
      Set objWordDoc = Nothing
      objWordApp.Quit SaveChanges:=False
      set objWordApp = Nothing
    In the above, you created a new instance of Word. It's also possible to check if Word is already running, and if so, to use that instance. More complicated, but more efficient.

    Sub ManipulateWord()

    Dim blnStartWord As Boolean
    Dim objWordApp As Word.Application
    On Error Resume Next
    ' Check if Word is active
    Set objWordApp = GetObject(, "Word.Application")
    If objWordApp Is Nothing Then
    ' No, we must start Word
    Set objWordApp = CreateObject("Word.Application")
    blnStartWord = True
    End If

    On Error Goto Err_Handler
    ' your code to manipulate Word goes here

    On Error Resume Next
    If blnStartWord Then
    ' We started Word, so we must quit it too.
    objWordApp.Quit SaveChanges:=False
    End If
    ' Release object memory
    Set objWordApp = Nothing
    Exit Sub

    MsgBox Err.Description
    Resume Exit_Handler

    End Sub

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Melton Mowbray, UK
    Thanked 0 Times in 0 Posts

    Re: An Excel Macro to control actions in Word? (Office 97)

    Thanks for taking the trouble to provide such a clear explanation.
    Up to me know!


Posting Permissions

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