
vba script in excel to perform manipulations in excel
I have setup a
VBA macro in excel to copy a series of text files into
a single word document. I chose the excel front end because I have a
series of other macros that generate the text files based on excel
spreadsheets, and a simple DOS program, and I wanted to keep the
interface in one place for ease of use.
Everything seems to work well except that, when I finish running my
macro, I can't access the menus in Word. If I then select anything in
excel, the problem seems to solve itself. I tried to add another line
of code that simply selects something in excel, but that doesn't solve
the problem. Any ideas?
Here is the code...
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim Doc2 As Word.Document
files = Application.GetOpenFilename("Output Files (*.out),*.out", 1,
"", "", True)
On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If Err Then
Set WordApp = New Word.Application
WordWasNotRunning = True
End If
On Error GoTo Err_Handler
WordApp.Visible = True
WordApp.Activate
Set WordDoc = WordApp.Documents.Add
'Activate excel so that you don't need to watch the word documents
flashing by
AppActivate ("Microsoft Excel")
For Each Value In files
Set Doc2 = WordApp.Documents.Open(Value)
Doc2.Activate
WordApp.Selection.WholeStory
WordApp.Selection.Copy
WordDoc.Activate
WordApp.Selection.EndKey Unit:=wdStory
WordApp.Selection.Paste
WordApp.Selection.InsertBreak Type:=wdPageBreak
Doc2.Close
Next
WordDoc.Activate
WordApp.Selection.WholeStory
WordApp.Selection.Font.Name = "Courier"
WordApp.Selection.Font.Size = 8
WordApp.Selection.EndKey Unit:=wdStory
WordApp.Selection.TypeBackspace
Set WordApp = Nothing
Set WordDoc = Nothing
Set Doc2 = Nothing
Exit Sub
Err_Handler:
MsgBox "Word caused a problem. " & Err.Description, vbCritical,
"Error: " _
& Err.Number
If WordWasNotRunning Then
WordApp.Quit
End If
End Sub