VBA: formatting a word document/RTF file from VBA

[mh2020]

I am trying to format a word document saved as an RTF from VBA after already posting data into it in the same macro.
The path output I am using is saved as a string and not an object which I think might be causing the problem but im not sure. My code is below.


Option Explicit


Sub MyMacro()


Dim TargetPath As String
Dim TargetFile As String
Dim OutputPath As String
Dim PrintableOutputFile As String
Dim PrintOutput As String
Dim PathInput As String






TargetPath = Range("Target_Path").Value
TargetFile = Range("target_file").Value
OutputPath = Range("Output_Path").Value
PrintableOutputFile = Range("printable_output_file").Value


PathInput = Range("target_path").Value & "" & Range("target_file").Value
PrintOutput = Range("output_path").Value & "" & Range("printable_output_file").Value


Open PathInput For Input As #1
Open PrintOutput For Output As #2

Print #2, "My Data here"


Close #1
Close #2


End Sub





The values in quotations are saved named ranges in my excel document with the location and names of the files I am using.
I am trying to format the word document to have font size of 14 and top/bottom margins of 2.25.
My guess so far was to use the WITH function in VBA but I was not having any luck.

'With PrintOutput.Object.PageSetup
' .TopMargin = wrdApp.InchesToPoints(2.25)
' .BottomMargin = wrdApp.InchesToPoints(2.25)
' .Font.Size = 14
'End With


If anyone has any tips or any other advice in general I would greatly appreciate it.

Thanks! :)

[Kenneth Hobs]

Welcome to the forum! Please paste code between code tags. Click the # icon on reply toolbar to insert the tags.

The only way that would work is to open the file in binary mode and add binary data.

The easier method is to open it as a Word document and then use Word methods to update it. This is the power of a common Suite like Office. There are many examples.


'Automate MSWord from another VBA Application like Excel' http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=169:automate-microsoft-word-from-excel-using-vba&catid=79&Itemid=475


'TypeText method
' http://www.excelforum.com/excel-programming/650672-populate-word-document-from-excel.html#post1946784
' http://www.excelforum.com/showthread.php?p=1946784
' http://vbaexpress.com/forum/showthread.php?p=169877
' http://vbaexpress.com/forum/showthread.php?t=24693
' http://www.excelforum.com/excel-programming/791302-excel-to-word-paragraph-and-page-setup.html
' http://www.vbaexpress.com/forum/showthread.php?50947

[mh2020]

Thanks for those links, they were helpful.
I have now figured out a way to update the margins but it is just the font part I am stuck on.
This is what I have so far.




Option Explicit


Sub MyMacro()


Dim TargetPath As String
Dim TargetFile As String
Dim OutputPath As String
Dim PrintableOutputFile As String
Dim PrintOutput As String
Dim PathInput As String






TargetPath = Range("Target_Path").Value
TargetFile = Range("target_file").Value
OutputPath = Range("Output_Path").Value
PrintableOutputFile = Range("printable_output_file").Value


PathInput = Range("target_path").Value & "\" & Range("target_file").Value
PrintOutput = Range("output_path").Value & "\" & Range("printable_output_file").Value


Open PathInput For Input As #1
Open PrintOutput For Output As #2


Print #2, "My Data here"


Set wrdApp = GetObject(PrintOutput)


With wrdApp.PageSetup
.TopMargin = Application.InchesToPoints(2.25)
.BottomMargin = Application.InchesToPoints(2.25)
' .Font.Size = 14
End With


Close #1
Close #2


End Sub




Running this with the font command gives an error message "Object doesnt support this property or method".
If you or anyone else has any tips or anything that could help please let me know, any feedback is appreciated.
Thanks!

[gmayor]

Font is not an attribute associated with page setup but with the range e.g.


With wrdapp
With .PageSetup
.TopMargin = Application.InchesToPoints(2.25)
.BottomMargin = Application.InchesToPoints(2.25)
End With
.Range.Font.Size = 14
End With

[mh2020]

Awesome, thank you! I ended up using the CONTENT command instead. I am now using the same CONTENT command to try and adjust the paragraph format for the .RTF document.
This is what I have so far.


With wrdApp
.PageSetup.TopMargin = Application.InchesToPoints(2.25)
.PageSetup.BottomMargin = Application.InchesToPoints(2.25)
.Content.Font.Size = 14
.Content.ParagraphFormat.LineSpacingrule = wdlinespaceExactly
.Content.ParagraphFormat.linespacing = 5.75
End With


For some reason the paragraph formatting does not work. Do you know if I should do this outside of the WITH function?
Any help or tips anyone has would be greatly appreciated.
Thanks!

[gmayor]

As you are using late binding to Word, you need to set the numeric value of wdLineSpacing i.e. 4
the Linespacing is in points and 5.75 is a ridiculously small size for a 14 point font and will produce lines that overwrite one another.

With wrdApp
.PageSetup.TopMargin = Application.InchesToPoints(2.25)
.PageSetup.BottomMargin = Application.InchesToPoints(2.25)
.Content.Font.Size = 14
.Content.ParagraphFormat.LineSpacingRule = 4
.Content.ParagraphFormat.LineSpacing = 5.75
End With

[mh2020]

Wow god bless you, you are a saint.

-How did you know that linespacingrule needed a numerical digit though? As opposed to typing in "wdlinespaceexact" ?

-You mentioned that this is using late binding to word, is there a better way to format data like this?

-Also this WITH function is cutting off some of my data at the end of my document which only happens when the WITH function is in the code. Are there any inherit properties of this function that would cause something like that?

Any help or advice would be greatly appreciated as I am still learning and am working on this project by myself.
Thanks again.


Edit: also you were right, I ended up changing the font size to 7

[gmayor]

wdlinespaceexact is a Word function. It appears that your are using Excel VBA with late binding to the Word object. This requires that you use numeric equivalents of Word specific commands.

As for the rest there is no limit of the kind you imply. Without access to the documents and your code to test your procedures we would only be guessing what the problem might be.

[mh2020]

I figured it out now, I ended up having to add a 60 character space at the end of the document so that all my data would print. Not sure why.
Figured Id post it here in case anyone else has any similar problems.
Thanks again for the help.

edit:

Here is the final WITH statement I used in my code in case itll help anyone else.



Set wrdApp = GetObject(PrintOutput)
With wrdApp
.PageSetup.TopMargin = Application.InchesToPoints(0.25)
.PageSetup.BottomMargin = Application.InchesToPoints(0.25)
.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
.PageSetup.RightMargin = Application.InchesToPoints(0.25)
.Content.Font.Size = 7
.Content.ParagraphFormat.SpaceBefore = 0
.Content.ParagraphFormat.SpaceAfter = 0
.Content.ParagraphFormat.SpaceBeforeauto = False
.Content.ParagraphFormat.Spaceafterauto = False
.Content.ParagraphFormat.LineSpacingrule = 4
.Content.ParagraphFormat.linespacing = 5.75
End With