Backgroud: The salesmen put workorders in on their iPads and my excel workorder goes out and gets that data and creates each workorder one at a time - placing all the data on the page, getting any maps and adding them to the workorder, etc. So - when I go out and get the map, I place it in a word document that has the margins and images expanded to maximize the fit on the page. Once I have the map page formatted 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 is manipulating the word document margins or the word document header... Once the code has failed, it will not execute that piece of code until I restart the computer...

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

Here is the code:

Sub WorkOrderMaps()

Dim i As Integer
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdRng As Word.Range
Set wrdApp = CreateObject("Word.Application")

wrdApp.Visible = False

CloseWord = False
If Map1 < "" Then 'Print Map 1 to PDF
Map = Map1
MapName = WorkOrderNumberForTarget & " - Map1.jpg"
MapText = WorkOrderNumberForTarget & " - Map1"
Call DownloadMap

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

'*** Spread the margins out to get as much use out of the page as possible
'*** 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 for
Set wrdRng = wrdDoc.Sections(1).Headers(wdHeaderFooterPrimary). Range
wrdRng = "MyCompany WO " & MapName
wrdRng.ParagraphFormat.Alignment = wdAlignParagraphCenter
wrdRng.Font.Size = 22
wrdRng.Font.Bold = wdToggle

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

'*** If the image is wider then print it in Landscape mode
If myImg.Height < myImg.Width Then
wrdDoc.PageSetup.Orientation = wdOrientLandscape
With wrdDoc.InlineShapes(1)
.LockAspectRatio = msoTrue
.Width = InchesToPoints(9) 'Make the image FILL the screen
End With
Else '*** If the image is taller then print it in Portrait mode
wrdDoc.PageSetup.Orientation = wdOrientPortrait
With wrdDoc.InlineShapes(1)
.LockAspectRatio = msoTrue
.Height = InchesToPoints(9) 'Make the image FILL the screen
End With
End If

'*** 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:=False

'***Remove the Downloaded Map
Kill KillJPGFile

'***Close Word without Saving
wrdDoc.Saved = True
wrdDoc.Close

End If

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
Set wrdRng = Nothing
Set myImg = Nothing

End Sub

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

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 :
Backgroud: The salesmen put workorders in on their iPads and my excel
workorder goes out and gets that data and creates each workorder one at
a time - placing all the data on the page, getting any maps and adding
them to the workorder, etc. So - when I go out and get the map, I place
it in a word document that has the margins and images expanded to
maximize the fit on the page. Once I have the map page formatted
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 is
manipulating the word document margins or the word document header...
Once the code has failed, it will not execute that piece of code until I
restart the computer...

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

Here is the code:

Sub WorkOrderMaps()

Dim i As Integer
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdRng As Word.Range
Set wrdApp = CreateObject("Word.Application")

wrdApp.Visible = False

CloseWord = False
If Map1 < "" Then 'Print Map 1 to PDF
Map = Map1
MapName = WorkOrderNumberForTarget & " - Map1.jpg"
MapText = WorkOrderNumberForTarget & " - Map1"
Call DownloadMap

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

'*** Spread the margins out to get as much use out of the page
as possible
'*** 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 for
Set wrdRng =
wrdDoc.Sections(1).Headers(wdHeaderFooterPrimary). Range
wrdRng = "MyCompany WO " & MapName
wrdRng.ParagraphFormat.Alignment = wdAlignParagraphCenter
wrdRng.Font.Size = 22
wrdRng.Font.Bold = wdToggle

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

'*** If the image is wider then print it in Landscape mode
If myImg.Height < myImg.Width Then
wrdDoc.PageSetup.Orientation = wdOrientLandscape
With wrdDoc.InlineShapes(1)
.LockAspectRatio = msoTrue
.Width = InchesToPoints(9) 'Make the image FILL the
screen
End With
Else '*** If the image is taller then print it in Portrait mode
wrdDoc.PageSetup.Orientation = wdOrientPortrait
With wrdDoc.InlineShapes(1)
.LockAspectRatio = msoTrue
.Height = InchesToPoints(9) 'Make the image FILL the
screen
End With
End If

'*** 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:=False

'***Remove the Downloaded Map
Kill KillJPGFile

'***Close Word without Saving
wrdDoc.Saved = True
wrdDoc.Close

End If

wrdApp.Quit
Set wrdDoc = Nothing
Set wrdApp = Nothing
Set wrdRng = Nothing
Set myImg = Nothing

End Sub