Consulting

Results 1 to 9 of 9

Thread: VBA: formatting a word document/RTF file from VBA

  1. #1
    VBAX Regular
    Joined
    Mar 2019
    Posts
    9
    Location

    VBA: formatting a word document/RTF file from VBA

    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!

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,751
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Mar 2019
    Posts
    9
    Location
    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!

  4. #4
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Regular
    Joined
    Mar 2019
    Posts
    9
    Location
    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!

  6. #6
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Regular
    Joined
    Mar 2019
    Posts
    9
    Location
    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

  8. #8
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  9. #9
    VBAX Regular
    Joined
    Mar 2019
    Posts
    9
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •