PDA

View Full Version : Is this the right code ?



JPDO
10-30-2004, 05:16 AM
Hello,
Can any one confirm me if this is the right VBA-code to create bookmark "FidatDEB" on a new page behind all other info and the right way to paste the content of the XLS-Sheet "ELFI" into that bookmark ?
BTW is there a vba-statement in WORD to fit this XLS-SHEET on 1 page ?

Thanks for any reply.

This is my code:
Public Sub Test()
Dim WdApp As Word.Application
Dim WdDoc As Word.Document
Set WdApp = New Word.Application
Set WdDoc = New Word.Document
Set WdDoc = WdApp.Documents.Add("c:\data\checklist.dot", False, wdNewBlankDocument, True)
WdDoc.Activate
WdDoc.Unprotect
' search last paragraph + do a page break
WdDoc.paragraphs(WdDoc.paragraphs.Count).Range.insertparagraphafter
WdDoc.paragraphs(WdDoc.paragraphs.Count).Range.insertbreak Type:=wdsectionbreaknextpage
' position cursor on first character of the new page
WdDoc.paragraphs(WdDoc.paragraphs.Count).Range.Select
WdDoc.FormFields.Add Range:=WdDoc.paragraphs(WdDoc.paragraphs.Count).Range, _
Type:=wdfieldformtextinput
' insert bookmark with name FidatDeb
With Dialogs(wddialogformfieldOptions)
.Name = "FidatDeb"
.Execute
End With
' set the content of worksheet ELFI as result of bookmark FidatDeb
Worksheets("ELFI").Activate
ActiveSheet.UsedRange.Select
Selection.Copy
WdDoc.paragraphs(WdDoc.paragraphs.Count).Range.Paste
' does the result of the bookmark FidatDeb now contains Worksheet ELFI ?
' How can i force Word to fit the result upon 1 page (cfr Fit to page in Excel)
WdDoc.Protect Type:=wdAllowOnlyFormFields, noreset:=False, Password:=""
WdDoc.SaveAs Filename:="c:\data\test.doc"
WdApp.Quit
Set WdDoc = Nothing
Set WdApp = Nothing
End Sub
:help :help

Bilby
11-04-2004, 02:23 PM
Hi,

I tried out your code using Office 2003 and found that it almost worked on my setup.

1. Ensure that you make a reference to the Word Object Library
2. It'll break if the created document isn't already protected so add an error handler ( such as on error resume next)
3. Change your With Dialogs..... line to With WdApp.Dialogs ......

A quick method to reach the beginning of a Word Document is to use
Selection.HomeKey Unit:=wdStory ... (modify to use your refs)

Bilby