Inserting Text in specific Locations

[swoozie]

I am not familiar with VBA and word at all. Infact it seems very confusing. You guys helped me great with my chart copy and paste issue, now after reading and looking I am just as confused looking into I think bookmarks. Here is what I am trying to do with those 6 charts your site helped me with.

I have a document which needs to look like this:

Title
Mth & Year (In a variable)
<Space>Page 1
Text explaining the charts (Usually around 3 lines of text with calculations included (I figured, just insert the coded text)
<Space>
Additional text explaining the charts.
(Usually 2 lines)
<Space>
Additional text explaining even more information on the charts
(Usually 2 lines)
<Space>
Chart1

<space>
Chart2

<PAGEBREAK>
Page 2
Text explaining the charts (Usually around 3 lines of text with calculations included (I figured, just insert the coded text)
<Space>
Additional text explaining the charts.
(Usually 2 lines)
<Space>
Additional text explaining even more information on the charts
(Usually 2 lines)
<Space>
Chart3

<space>
Chart4

<PAGEBREAK>
Page 3
Text explaining the charts (Usually around 3 lines of text with calculations included (I figured, just insert the coded text)
<Space>
Additional text explaining the charts.
(Usually 2 lines)
<Space>
Additional text explaining even more information on the charts
(Usually 2 lines)
<Space>
Chart5

<space>
Chart6

<PAGEBREAK>

I do not understand how to insert the text into the correct places so that the pages always number 3.

Thank you!

[fumei]

You need to do a good analysis.

Is this pretty much how it is going to be? This kind of "physical" layout?

How are you doing the <space>? What does that mean? The "space" between paragraphs? I hope you are not doing this with "extra" Enter key presses.

This sounds like a perfect use for a template.

This "always number 3" is confusing. Why would it NOT be 3??????

[swoozie]

That is the way it needs to look. That is the physical layout. It should only be 3 pages long.

I do not know how to create a template. I don't use word for reporting, heck I dont use word for much of anything, but this department asked me to automate a report and their final emailed out document is a word doc and they will not change. So they get 1 word doc and 2 excel spreadsheet instead of just doing the whole thing in excel.

So basically I am lost at this point. You know excel you specifiy where to paste something. Word doenst appear to be that simple.

[blake.arnold]

What is passing this information to the report. For instance when you say Title; Does the user type in the name of the title or do you want the code to get this from another place.

Also do you want to build this report with code or do you just want code to edit the report?

I think fumei was right in recomending a template because there is so much that you can do with one. This page seems to give some good info on Word Templates. http://wordprocessing.about.com/od/usingtemplatesandaddins/l/blwordtemp.htm

Check that out and see if it will meat your needs.

[swoozie]

What is passing this information to the report. For instance when you say Title; Does the user type in the name of the title or do you want the code to get this from another place.

Also do you want to build this report with code or do you just want code to edit the report?

I think fumei was right in recomending a template because there is so much that you can do with one. This page seems to give some good info on Word Templates. http://wordprocessing.about.com/od/usingtemplatesandaddins/l/blwordtemp.htm

Check that out and see if it will meat your needs.
Thanks for the Link! I really wanted to build the entire thing in code so I only have one file to worry about when I hand it off, but If I should use a template I will.

There is nothing in the document being created that will be manually put in. all pieces can be created via code. I just don't know how to place it in word. If I could have left it in my Excel spreadsheet I would have been done weeks ago.

[swoozie]

Thanks for the Link! I really wanted to build the entire thing in code so I only have one file to worry about when I hand it off, but If I should use a template I will.

There is nothing in the document being created that will be manually put in. all pieces can be created via code. I just don't know how to place it in word. If I could have left it in my Excel spreadsheet I would have been done weeks ago.
Question about the templates. When you create one it needs to be saved to a specific file location for templates correct?

C:Whatever all this could be\Application Data\Microsoft\Templates

Guess what, I was not provided access to save, read, open, visit, ect... This folder. Is there a workaround?

[blake.arnold]

I would deffinatly recomend creating a template. There may be some code that you will need to add to it at the end but a template will be much easier.

As for handing over one document you can distribute the template to anyone you see fit. Also for those who are less than computer savey in your orginization templates allow a very intuitive user interface.

Once you get the template done just ask if you need some help with code.

[blake.arnold]

You can save your template anywhere. When you need to open it just click File>Open and then specify a .dot document and you will be able to open it.

I think that the folder that you refer to
C:Whatever all this could be\Application Data\Microsoft\Templates will put it in the template wizard everytime.

[swoozie]

What would you suggest for creating the Chart place holders? I do not see any options that appear suitable.

[blake.arnold]

Are you importing this chart from Excel?

[swoozie]

Okay another question, sorry I did not save them all for one post.

How do I open the template and paste to it from excel?

[blake.arnold]

I knew you were going to ask that. So I think this will work for you.

1. In Excel, select the graph you wish to use. 2. Click Edit | Copy on the menu bar

http://www.school.za/PILP/skill/word2002/importchart1.jpg

3. In Word, Position the cursor in the document where you'd like to put the graph.
4. Click Edit | Paste Special

http://www.school.za/PILP/skill/word2002/importchart2.jpg
The following box will appear:

http://www.school.za/PILP/skill/word2002/importchart3.jpg
5. Select the option Paste Link. This will place the picture in your document, but also maintain a link to the Excel Graph.
Any changes that are made to the Excel spreadsheet will reflect in the Word document each time it is opened. The Excel sheet must be saved before Word will see any changes.
If open the document in Word and want to check that this is the latest version of the graph, right-click the graph in word, and select Update Link.

[swoozie]

I dont want to have anything linked to anywhere else, this needs to be a complete independent document.

This is the Code I am using for the copy and paste. Look at the remarks below:
Application.ScreenUpdating = False

NewBook = ActiveWorkbook.Name

Set myWd = CreateObject("Word.Application")

myWd.Documents.Add


Windows(NewBook).Activate
Sheets("CHART").Select
For i = 1 To 6
CName = "Chart " & i
Sheets("Chart").ChartObjects(CName).Activate
Sheets("Chart").ChartObjects(CName).Copy
'**************
'I would like to be able to paste the charts and text at the appropriate book marks. you should be able to reference them by something like
'myWd.bookmarks.name:= "Chart1" or
'Mywd.selection.goto What ;= wdgotobookmark, name:="Chart1"

myWd.Selection.Paste
Next i

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Path = "Documents and Settings\c-smiller\My Documents\ATS Project Daubert\"
MyFile = "Monthly_ATS_Summary" & SName1
Ext = ".doc"
Drive = "c:\"


MyFile = Drive & Path & MyFile & Ext

myWd.ActiveDocument.SaveAs FileName:=MyFile, FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False

myWd.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

myWd.Quit
Set myWd = Nothing
Application.ScreenUpdating = True

If Len(Dir(MyFile)) > 0 Then
MsgBox MyFile & " created"
End If
End Sub

[swoozie]

I dont want to have anything linked to anywhere else, this needs to be a complete independent document.

This is the Code I am using for the copy and paste. Look at the remarks below:
Application.ScreenUpdating = False

NewBook = ActiveWorkbook.Name

Set myWd = CreateObject("Word.Application")

myWd.Documents.Add
'myWd.documents.add ("C:\TemplateLocation")

Windows(NewBook).Activate
Sheets("CHART").Select
For i = 1 To 6
CName = "Chart " & i
Sheets("Chart").ChartObjects(CName).Activate
Sheets("Chart").ChartObjects(CName).Copy
'**************
'I would like to be able to paste the charts and text at the appropriate book marks. you should be able to reference them by something like
'myWd.bookmarks.name:= "Chart1" or
'Mywd.selection.goto What ;= wdgotobookmark, name:="Chart1"

myWd.Selection.Paste
Next i

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Path = "Documents and Settings\c-smiller\My Documents\ATS Project Daubert\"
MyFile = "Monthly_ATS_Summary" & SName1
Ext = ".doc"
Drive = "c:\"


MyFile = Drive & Path & MyFile & Ext

myWd.ActiveDocument.SaveAs FileName:=MyFile, FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False

myWd.Documents.Close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

myWd.Quit
Set myWd = Nothing
Application.ScreenUpdating = True

If Len(Dir(MyFile)) > 0 Then
MsgBox MyFile & " created"
End If
End Sub

[blake.arnold]

So based on your code you are running this from an excel workbook. That means that this is going to be a static graph and not a dynamic graph? If you dont ever want this graph to change then why go through all that work? Why not just do a one time copy and paste?

I think that you want this graph to be dynamically updated thought. So then how do you plan on doing this from word? Or do you just want to count on the fact that any user that opens this template will have the required spreedsheet open also?

You are going to have to clarify what you plan on using this graph for.

[swoozie]

This report is ran once a month. The output is 1 excel spreadsheet and 1 word document. I already create all the necesary items in excel, however they still want their word document. So I am trying to figure out how to paste the information from excel into the word document, no links, just the pictures and the text. I can paste the charts but they just go over one after the other. I want them to go over, the text to go over and to insert the pagebreaks and etc.. into the word document. I wanted the entire document created in code via excel vba.

[blake.arnold]

Ok so there is nothing that is going into the document that is not already contained within the spreedsheet?

Is the info always going to be in the same cells?

[swoozie]

Pretty much, the text is in a string to be pasted since it has calulcations through out it and charts are in the spreadsheet.

[blake.arnold]

Ok. Why dont you just capture the different sections in variables within your spreedsheet and then build you document from those.

Example:

Dim varSales as String
varSales = Range("E14").text
ActiveDocument.Select
Selection.TypeText (varSales)
Selection.TypeParagraph
Selection.TypeParagraph
Selection.InsertBreak Type:=wdPageBreak

Give me some time on the graph I am checking to see how to do that.

[blake.arnold]

Ok here you go. I have not been able to get the chart formating right but I will work on it latter. I threw this togetther quick so please go back through and edit it. Also I noticed that you did not define any of your variables in the last piece of code that you posted and I wanted to remind you to do that.

Dim NewBook
NewBook = ActiveWorkbook.Name

Dim varNumber As String
varNumber = Range("E16").Text

Dim myWd
Set myWd = CreateObject("Word.Application")
myWd.Documents.Add

Windows(NewBook).Activate

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Copy
myWd.Selection.TypeText (varNumber)
myWd.Selection.TypeParagraph
myWd.Selection.TypeParagraph
myWd.Selection.PasteAndFormat (wdChartPicture)

Dim MyFile as String
Dim Ext as String
Dim Drive as String

MyFile = "Monthly_ATS_Summary"
Ext = ".doc"
Drive = "c:\"


MyFile = Drive & MyFile & Ext

myWd.ActiveDocument.SaveAs Filename:=MyFile, FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False

myWd.Documents.Close


myWd.Quit
Set myWd = Nothing
Application.ScreenUpdating = True