K
Kate
Hi,
I have a complicated application that interfaces Access, Excel, and
Word. The critical issue, when upgrading to Office 2007 from Office 2003,
is that the Word document layout changes. This is part of the process,
via VBA:
1. Excel creates multiple charts for a company, based upon data sets
pasted in from Access.
2. It opens a Word template and pastes the charts at the correct
bookmark for each chart,
resizing them to 500 wide x 300 tall. This worked fine in W2003,
placing two charts/page.
The document size was ~400 kb.
3. In W2007, only one chart is appearing/page. I manually decreased
the margins to 'narrow' all around,
and got two charts/page expect for the first page of a new group, where
a line of text appears at the top and
only one chart now shows on that page. There are 7 such groups. The
size of the doc is now ~2750 kb!
If I save it as a docx, it decreases only to ~2650 kb.
Why the blooming size increase? What to do about it?
Here's the code snippet from Excel:
Dim wdApp As Word.Application
'Establish link to Word
Set wdApp = New Word.Application
With wdApp
Dim strBkmk As String
'Open Word document
.Documents.Open Filename:=flname
' copies plots from excel into word document
If xlbook.Application.Charts.Count > 0 Then
For Each s In xlbook.Application.Charts
' copies plots from excel into word document, pasting at
bookmarks that match chart names
s.Activate
'extract name of Word bookmark from chart name by
removing category abbrev.
strBkmk = Left(s.Name, Len(s.Name) - 5)
'resize
s.PageSetup.ChartSize = xlScreenSize
s.ChartArea.Width = 500
s.ChartArea.Height = 350
s.ChartArea.Copy
If .ActiveDocument.Bookmarks.Exists(strBkmk) Then
With .Selection
.GoTo What:=wdGoToBookmark, Name:=strBkmk
.Collapse (wdCollapseStart)
'move up to the next paragraph mark, pre-set in
the template
.MoveLeft Unit:=wdCharacter, Count:=1
'if more than one of each chart might be
printed,insert another paragraph mark
If strType = "corp" And strBkmk <> "AOX" Then
.TypeParagraph
.MoveLeft Unit:=wdCharacter, Count:=1
End If
End With
'insert a new bookmark for that parameter's
production category
.ActiveDocument.Bookmarks.Add s.Name
'paste chart at bookmark, special, to save space
.Selection.PasteAndFormat (wdChartPicture)
Thanks,
Kate
I have a complicated application that interfaces Access, Excel, and
Word. The critical issue, when upgrading to Office 2007 from Office 2003,
is that the Word document layout changes. This is part of the process,
via VBA:
1. Excel creates multiple charts for a company, based upon data sets
pasted in from Access.
2. It opens a Word template and pastes the charts at the correct
bookmark for each chart,
resizing them to 500 wide x 300 tall. This worked fine in W2003,
placing two charts/page.
The document size was ~400 kb.
3. In W2007, only one chart is appearing/page. I manually decreased
the margins to 'narrow' all around,
and got two charts/page expect for the first page of a new group, where
a line of text appears at the top and
only one chart now shows on that page. There are 7 such groups. The
size of the doc is now ~2750 kb!
If I save it as a docx, it decreases only to ~2650 kb.
Why the blooming size increase? What to do about it?
Here's the code snippet from Excel:
Dim wdApp As Word.Application
'Establish link to Word
Set wdApp = New Word.Application
With wdApp
Dim strBkmk As String
'Open Word document
.Documents.Open Filename:=flname
' copies plots from excel into word document
If xlbook.Application.Charts.Count > 0 Then
For Each s In xlbook.Application.Charts
' copies plots from excel into word document, pasting at
bookmarks that match chart names
s.Activate
'extract name of Word bookmark from chart name by
removing category abbrev.
strBkmk = Left(s.Name, Len(s.Name) - 5)
'resize
s.PageSetup.ChartSize = xlScreenSize
s.ChartArea.Width = 500
s.ChartArea.Height = 350
s.ChartArea.Copy
If .ActiveDocument.Bookmarks.Exists(strBkmk) Then
With .Selection
.GoTo What:=wdGoToBookmark, Name:=strBkmk
.Collapse (wdCollapseStart)
'move up to the next paragraph mark, pre-set in
the template
.MoveLeft Unit:=wdCharacter, Count:=1
'if more than one of each chart might be
printed,insert another paragraph mark
If strType = "corp" And strBkmk <> "AOX" Then
.TypeParagraph
.MoveLeft Unit:=wdCharacter, Count:=1
End If
End With
'insert a new bookmark for that parameter's
production category
.ActiveDocument.Bookmarks.Add s.Name
'paste chart at bookmark, special, to save space
.Selection.PasteAndFormat (wdChartPicture)
Thanks,
Kate