Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Creating a Temporary VB Macro in Word

    Introduction

    Iíve written a system for a user that has one ugly part that Iíd very much like to eliminate. The system is written in Excel and Word 2003, under Windows XP. I have poor Visual Basic Skills. Most of the application is written using the old Excel macro language, but my request is only about Visual Basic.

    My Requirement

    To create a Microsoft Word Visual Basic macro that copies in the contents of the clipboard to a new macro within Word, executes that new macro, and then deletes it.

    Background

    My user is a poet. She has about a hundred poems in different Word files in different folders. The files and folders may change. She wishes to aggregate these into ONE Microsoft Excel file so that she can analyse for repetitions etc. She is competent in Excel and Word, but not an expert. Iíve written a system for her that works in four steps as follows:

    Step ONE She presses a button in my Excel system and it generates a series of Word macro commands of the form:

    GetTextA mySet:="Poems", myPath:="C:\PoetryFolder\", myFile:="Hints on Writing Lyrics.doc"

    (GetText is a function of mine that opens the nominated file and reads in the file together with a header and footer and then closes the file.).

    Step TWO This is the ugly bit. She goes to Word and replaces a line in Visual Basic with the many lines generated by Excel. This is potentially dangerous for her.
    Step THREE She returns to Word and initiates the Macro ProcessText. This generates the concatenated Word file and puts it onto the clipboard.
    Step FOUR She returns to Excel and presses another button. This brings the clipboard into a Excel new file, edits it in various ways and presents this as the result.

    Corollary requests

    •  Within Word how does one close the Word file without generating a ďDo you want to save the changesĒ message?
    •  Within Excel, how can one close the Word application if it is open?

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    This can be done without the user interacting with Word at all. It is pretty straightforward to create a macro in Excel that automates Word and puts this content back in Excel for you. For an automation example see
    http://dailydoseofexcel.com/archives...tomating-word/

    For an example of creating vba code on the fly
    http://www.cpearson.com/excel/vbe.aspx
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Andrew,

    Thank you for your references. I've spent some time looking at them and I think they're beyond my skill level to make the necessary extrapolations. It's great news though that Word can be controlled from Excel. I see that you live in Melbourne, as do I. Perhaps we don’t live far apart!

    Regards

    Geoffrey

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Geoffrey

    The extrapolations are not that great and I'm sure you could work it out if you have coded in the old macro languages previously. If you want to make an attempt at adapting that code then you can post your results here and we can help you resolve any issues. There are lots of great examples using Excel to drive Word on the net if you know a few of the key words to search for.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    Antediluvian (2013-08-22)

  6. #5
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Andrew,

    Thank you. I'll put this on my to do list.

    Geoffrey

Posting Permissions

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