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?
