Working with Word Documents from within an Excel Macro

[jbrenner51]

Backgroud: The salesmen put workorders in on their iPads and my exce
workorder goes out and gets that data and creates each workorder one a
a time - placing all the data on the page, getting any maps and addin
them to the workorder, etc. So - when I go out and get the map, I plac
it in a word document that has the margins and images expanded t
maximize the fit on the page. Once I have the map page formatte
correctly, I print it to a PDF file and close word

The Problem: is that my code goes through one iteration of 'Get Maps'
and then it seems to die in the second iteration when the code i
manipulating the word document margins or the word document header..
Once the code has failed, it will not execute that piece of code until
restart the computer..

I need to figure out how to either leave word open so that it will ru
through all my iterations THEN close - OR, I need to know how to clos
it properly (completely kill it) so that each time is like the firs
with a clean copy of word. Maybe I need to use a tool other than word t
accomplish this... Any suggestions would be great! TIA

Here is the code

Sub WorkOrderMaps(

Dim i As Intege
Dim wrdApp As Word.Applicatio
Dim wrdDoc As Word.Documen
Dim wrdRng As Word.Rang
Set wrdApp = CreateObject("Word.Application"

wrdApp.Visible = Fals

CloseWord = Fals
If Map1 <> "" Then 'Print Map 1 to PD
Map = Map
MapName = WorkOrderNumberForTarget & " - Map1.jpg
MapText = WorkOrderNumberForTarget & " - Map1
Call DownloadMa

'*** Load up and prep MS Wor
Set myImg = LoadPicture(CurrentPath & "MyCompany WO " & MapName
Set wrdDoc = wrdApp.Documents.Add ' create a new documen

'*** Spread the margins out to get as much use out of the pag
as possibl
'*** HERE IS WHERE THE CODE FAILS **
wrdDoc.PageSetup.TopMargin = InchesToPoints(0.5
wrdDoc.PageSetup.BottomMargin = InchesToPoints(0.5
wrdDoc.PageSetup.LeftMargin = InchesToPoints(0.5
wrdDoc.PageSetup.RightMargin = InchesToPoints(0.5
wrdDoc.PageSetup.HeaderDistance = InchesToPoints(0.25

'*** Add the header so you know which workorder this map is fo
Set wrdRng
wrdDoc.Sections(1).Headers(wdHeaderFooterPrimary).Rang
wrdRng = "MyCompany WO " & MapNam
wrdRng.ParagraphFormat.Alignment = wdAlignParagraphCente
wrdRng.Font.Size = 2
wrdRng.Font.Bold = wdToggl

'*** Add the map to the pag
wrdDoc.InlineShapes.AddPicture fileName:=CurrentPath
"MyCompany WO " & MapName, LinkToFile:=False, SaveWithDocument:=Tru

'*** If the image is wider then print it in Landscape mod
If myImg.Height < myImg.Width The
wrdDoc.PageSetup.Orientation = wdOrientLandscap
With wrdDoc.InlineShapes(1
.LockAspectRatio = msoTru
.Width = InchesToPoints(9) 'Make the image FILL th
scree
End Wit
Else '*** If the image is taller then print it in Portrait mod
wrdDoc.PageSetup.Orientation = wdOrientPortrai
With wrdDoc.InlineShapes(1
.LockAspectRatio = msoTru
.Height = InchesToPoints(9) 'Make the image FILL th
scree
End Wit
End I

'*** Document is now formatted correctly, so Create the PDF **
wrdDoc.ExportAsFixedFormat OutputFileName:=CurrentPath
"MyCompany WO " & MapText & ".PDF", ExportFormat:=
wdExportFormatPDF, OpenAfterExport:=False
OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument
From:=1, To:=1,
Item:=wdExportDocumentContent, IncludeDocProps:=True
KeepIRM:=True, CreateBookmarks:=wdExportCreateNoBookmarks
DocStructureTags:=True,
BitmapMissingFonts:=True, UseISO19005_1:=Fals

'***Remove the Downloaded Ma
Kill KillJPGFil

'***Close Word without Savin
wrdDoc.Saved = Tru
wrdDoc.Clos

End I

wrdApp.Qui
Set wrdDoc = Nothin
Set wrdApp = Nothin
Set wrdRng = Nothin
Set myImg = Nothin

End Su

 

[GS]

Why wouldn't you place the data/map directly in Excel, then manipulate
it there?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[isabelle]

hi ,

i do the following test and i do not have any error message.
i think the problem comes from the inserted image, perhaps you should be
crop (or resize) the image before making the layout of the document.

Sub WorkOrderMaps()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add ' create a new document

wrdDoc.PageSetup.TopMargin = InchesToPoints(1)
wrdDoc.PageSetup.BottomMargin = InchesToPoints(1)
wrdDoc.PageSetup.LeftMargin = InchesToPoints(1)
wrdDoc.PageSetup.RightMargin = InchesToPoints(1)
wrdDoc.PageSetup.HeaderDistance = InchesToPoints(0.25)

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

isabelle

Le 2013-03-08 11:58, jbrenner51 a écrit :