vba script in excel to perform manipulations in excel 
Author Message
 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



Sun, 03 Apr 2005 05:26:58 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Windows EXCEL VBA / MAC EXCEL VBA compatibility issues

2. Excel 97 VBA vs Excel 2000 VBA

3. Running excel add-in (added into excel - not access) in Access VBA

4. Controlling Excel from VBA after Output to Excel File

5. Outlook VBA - open Excel, read in two fields, send email with these, close excel

6. Compatibility Problem Using MS Excel 97 VBA on Excel 2000/XP - Causes crashes

7. VBA code in Excel 97 vs Excel 2002

8. VBA in Excel, about two XLA in Excel

9. Help Excel 1997 to Excel 2000 VBA references

10. Running VBA code in EXcel from VB6 using EXcel Objects

11. VBA for Excel, nice homepage at http://www.100folhas.pt/software/excel.html

12. Excel - VBA - Retreive file without opening them in Excel

 

 
Powered by phpBB® Forum Software