Word/Excel help required

[sharrison]

I have a word document which is to be used as a quote document. What I want to be able to do is allow the salesperson to be able to choose, in Excel, which sections of the document they want to include. Depending on their selections I would like, via Excel, to be able to delete the pages which the salesperson doesn't want. Has anybody got any suggestions on how to do it. I tried recording a macro in Word to try and see how to do it but it appears that this macro doesn't translate into Excel (or at least I can't get it too). The recorded macro is as follows:

Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="3"
Selection.GoTo What:=wdGoToBookmark, Name:="\page"
Selection.Find.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Delete Unit:=wdCharacter, Count:=1

 

[sharrison]

I take it by the lack of replies nobody has come across this before

 

[Ruddles]

You can't just drop VBA from Word into Excel. You need to add a reference to the Microsoft Word Object Library (Excel > VBA editor > Tools > References), then in your VBA code you open an instance of MS Word, then open the Word document you want to manipulate, and finally issue those commands to Word so that Word can execute them.

If you Google vba excel word you will find many examples of this sort of thing. Unfortunately I've only done this once or twice - controlling Word from Excel - but I use the same method for controlling Access from Excel and it works fine.

Here's an example of how to create and save a Word document from Excel - which is about as much as I know about the subject:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub MakeWordDoc()[/FONT]
 
[FONT=Fixedsys] Dim wordApp As Word.Application[/FONT]
[FONT=Fixedsys] Set wordApp = CreateObject("Word.Application")[/FONT]
[FONT=Fixedsys] Dim wordDoc As Word.Document[/FONT]
[FONT=Fixedsys] Dim wordRange As Word.Range[/FONT]
 
[FONT=Fixedsys] With wordApp[/FONT]
 
[FONT=Fixedsys]   .WindowState = Word.WdWindowState.wdWindowStateMaximize[/FONT]
[FONT=Fixedsys]   .Documents.Add ("normal.dotm")[/FONT]
[FONT=Fixedsys]   Set wordDoc = .ActiveDocument[/FONT]
[FONT=Fixedsys]   Set wordRange = wordDoc.Range[/FONT]
 
[FONT=Fixedsys]   With wordRange[/FONT]
 
[FONT=Fixedsys]     .InlineShapes.AddPicture "C:\Temp\TEMP.PNG"[/FONT]
[FONT=Fixedsys]     .Move wdStory, 1[/FONT]
[FONT=Fixedsys]     .InlineShapes.AddHorizontalLineStandard[/FONT]
[FONT=Fixedsys]     .Move wdStory, 1[/FONT]
[FONT=Fixedsys]     .InsertAfter vbCrLf[/FONT]
 
[FONT=Fixedsys]     .Font.Bold = False[/FONT]
[FONT=Fixedsys]     .Font.Size = 12[/FONT]
[FONT=Fixedsys]     .ParagraphFormat.Alignment = wdAlignParagraphLeft[/FONT]
 
[FONT=Fixedsys]     .InsertAfter "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod "[/FONT]
[FONT=Fixedsys]     .InsertAfter "tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, "[/FONT]
[FONT=Fixedsys]     .InsertAfter "quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo "[/FONT]
[FONT=Fixedsys]     .InsertAfter "consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse "[/FONT]
[FONT=Fixedsys]     .InsertAfter "cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat "[/FONT]
[FONT=Fixedsys]     .InsertAfter "non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."[/FONT]
[FONT=Fixedsys]     .InsertAfter vbCrLf[/FONT]
 
[FONT=Fixedsys]     End With[/FONT]
 
[FONT=Fixedsys]   .ActiveDocument.SaveAs "C:\Temp\TEMP.DOC"[/FONT]
[FONT=Fixedsys]   .ActiveDocument.Close[/FONT]
[FONT=Fixedsys]   .Application.Quit[/FONT]
 
[FONT=Fixedsys]   Set wordDoc = Nothing[/FONT]
[FONT=Fixedsys]   Set wordApp = Nothing[/FONT]
 
[FONT=Fixedsys] End With[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

 

[sharrison]

Thanks Ruddles. I can do that bit and I can customised the quote document from job to job the only bit I am struggling with is the deleting of pages.

 

[Ruddles]

You're way ahead of me in that case!

Sorry I couldn't help.