Export Excel to Word

[Guest]

I am using Excel 97 and Word 97. I have the following routine which takes
data from Excel and places it into a word template. It searches for
Bookmarks and inserts the relevant data. I have one or two problems with it
and need some advice please.

1. At the end of the routine Word stays open and the document is not
visible.
2. The document name appears in the Word File menu and can be opened from
here.
3. When the document is opened it does not appear in print layout view, but
has a head/footer window open even though the last data inserted into the
document was placed in the body text of the document. The routine exporting
data from Excel does place some information in the header/footer against
given bookmarks.

Sub ExportToWord()
Dim WordBasic As Word.document
Dim WordFile As String
Dim CurrentDir As String
Dim FileToSave As String

On Error Resume Next
CurrentDir = Application.ActiveWorkbook.Path
FileToSave = Application.GetSaveAsFilename("Document 1 ", filefilter:="Word
Document (*.doc), *.doc")

If FileToSave = "" Then
Exit Sub
End If

' path and name of RT.dot
WordFile = Sheets("settings").Cells(20, 2).Value

Set WordBasic = GetObject(WordFile)
WordBasic.Application.Visible = False

With WordBasic
'ROUTINE STARTS HERE FOR PASTING DATA
If .Bookmarks.Exists("Name") = True Then
.Bookmarks("Name").Select
.ActiveWindow.Selection.text = frmSearch.txtName
End If
'< - Routine searching for bookmarks and inserting data here - >
End With

WordBasic.SaveAs (FileToSave)
Application.StatusBar = False
WordBasic.Application.Visible = True
Set WordBasic = Nothing

End Sub

 

[Bill Manville]

1. At the end of the routine Word stays open and the document is not

visible.

Try

WordBasic.Application.Quit
before
Set WordBasic = Nothing

2. The document name appears in the Word File menu and can be opened from
here.

Try
WordBasic.SaveAs AddToRecentFiles:=False

3. When the document is opened it does not appear in print layout view, but
has a head/footer window open even though the last data inserted into the
document was placed in the body text of the document.

Not sure about that one.
Maybe
WordBasic.Characters(1).Select

By the way, your choice of WordBasic as the name of the Word.Document object
was initially offputting since WordBasic was the name of the dialect of Basic
used in Word before Word 97.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Guest]

stephen-

i am trying to develop a macro to accomplish a similar excel-to-word export function. the particular action i want is as follows, which we are simply doing by hand currently
we copy a portions of various excel sprdshts ("ss") and use the paste-special feature in word to paste in as an excel
worksheet object. we then format as required, etc.

my question is, then, would you be able to suggest a template macro to accomplish this, or could you suggest (an) alternative means to capture the excel data/object in word and the requisite template macro(s)? thanks


----- (e-mail address removed) wrote: ----

I am using Excel 97 and Word 97. I have the following routine which take
data from Excel and places it into a word template. It searches fo
Bookmarks and inserts the relevant data. I have one or two problems with i
and need some advice please

1. At the end of the routine Word stays open and the document is no
visible
2. The document name appears in the Word File menu and can be opened fro
here
3. When the document is opened it does not appear in print layout view, bu
has a head/footer window open even though the last data inserted into th
document was placed in the body text of the document. The routine exportin
data from Excel does place some information in the header/footer agains
given bookmarks

Sub ExportToWord(
Dim WordBasic As Word.documen
Dim WordFile As Strin
Dim CurrentDir As Strin
Dim FileToSave As Strin

On Error Resume Nex
CurrentDir = Application.ActiveWorkbook.Pat
FileToSave = Application.GetSaveAsFilename("Document 1 ", filefilter:="Wor
Document (*.doc), *.doc"

If FileToSave = "" The
Exit Su
End I

' path and name of RT.do
WordFile = Sheets("settings").Cells(20, 2).Valu

Set WordBasic = GetObject(WordFile
WordBasic.Application.Visible = Fals

With WordBasi
'ROUTINE STARTS HERE FOR PASTING DAT
If .Bookmarks.Exists("Name") = True The
.Bookmarks("Name").Selec
.ActiveWindow.Selection.text = frmSearch.txtNam
End I
'< - Routine searching for bookmarks and inserting data here -
End Wit

WordBasic.SaveAs (FileToSave
Application.StatusBar = Fals
WordBasic.Application.Visible = Tru
Set WordBasic = Nothin

End Su