Search code examples
vbaexcelms-wordexcel-2003

Sending VARIABLES from EXCEL to WORD


I have this complex excel file that calls (opens) a doc file.

Now I want to be able to change a few part of the word file (no formatting change just the strings).

What I'm think of doing is:

  1. The excel file creates a txt file with each line representing the "vars" I want send to word.

  2. On opening word, the DOC execs a macro that reads each line form the txt and does a REPLACE for that var.

I'm think of having the vars on the doc like this:

Bla bla bla bla ?!-1-!? bla bla bla bla .... ?!-2-!?

Where ?!-1-!? and ?!-2-!? are the vars to replaced for lines 1 and 2 from the txt file

My questions are:

  1. Is this stupid?
  2. Is there a better way?
  3. and most importantly, is there a easier way?

Also: I'm good in vba for excel 2003, newbie on vba for word 2003. Any links on help with working with files on vba word?

TL;DR: Easiest way to send text variables form excel to word


Solution

  • Your idea is a good one but this is what you might use when the application you want to send information to does not "allow access". The good thing about MS Office is you do get to automate it. So I would suggest you automate Word from your Excel.

    First you should add a reference to the Microsoft Word Object Library" in your Excel project. Second to get word you would need something like this in your excel code.

    On Error Resume Next
       Set objWord = GetObject(, "Word.Application")
       If Err.Number = 429 Then
          'Word is not running; creating a Word object
          Set objWord = CreateObject("Word.Application")
          Err.Clear
       End If
    

    Next to get your word document something like this;

    Set objDocs = objWord.Documents
    objDocs.add("Path to your Word Documenbt")
    Set myDoc = objWord.ActiveDocument
    

    Next I choose to use CustomDocumentProperties

    Set prps = objWord.ActiveDocument.CustomDocumentProperties
    prps.item("YourCustomName").Value = "Your Variable from Excel"
    

    The hard bit is setting up your word document so here are a few links or tutorials on what I have suggested here.

    How to add CustomDocumentProperties Microsoft Example of how to use CustomDocumentProperties in VBA