VBA to Create Word Document and then copy multiple ranges to that document

[bisel]

Greetings,

I am a novice at VBA and hoping to get some help.

I have created the following subroutine ...

Sub Excel_to_Word()


    Dim appWord As Word.Application
    
    Set appWord = New Word.Application
    appWord.Visible = True
    
   
    With appWord
        Range("resfundwithdraw1").Copy
        appWord.Documents.Add.Content.Paste
    
        Range("resfundwithdraw2").Copy
        appWord.Documents.Add.Content.Paste
    
        Range("resfundwithdraw3").Copy
        appWord.Documents.Add.Content.Paste
    End With


End Sub

The above code creates a Word document, copies the named range then continues to copy a second Word document and pastes the named range, concluding with creation of third Word document and pasting the last named range.

What I am trying to do is to create one Word document and then copy and pasting all three named ranges into that single document.

Appreciate any help ...

Best regards,

Steve

 

[Macropod]

Try:

Sub Excel_to_Word()
Dim wdApp As New Word.Application, wdDoc As Word.document
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc
    Range("resfundwithdraw1").Copy
    .Range.Characters.last.Paste
    Range("resfundwithdraw2").Copy
    .Range.Characters.last.Paste
    Range("resfundwithdraw3").Copy
    .Range.Characters.last.Paste
  End With
End With
End Sub

 

[bisel]

Thank you for the assistance. This works well.

 

[bisel]

I am trying to do some formatting changes and such now. I am able to get the three tables from Excel pasted in the Word document, but the code that was suggested ...

Sub Excel_to_Word()
Dim wdApp As New Word.Application, wdDoc As Word.document
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc
    Range("resfundwithdraw1").Copy
    .Range.Characters.last.Paste
    Range("resfundwithdraw2").Copy
    .Range.Characters.last.Paste
    Range("resfundwithdraw3").Copy
    .Range.Characters.last.Paste
  End With
End With
End Sub

... from Macropod will copy the first Excel table (resfundwithdraw1) and paste in Word, but then the subsequent tables are copied and appended to that table in Word. I end up with one Word table. How can I copy each table in Excel and then end up with three individual tables in Word?

Also, is there a way that I can include in the Excel macro a way to change the layout in Word so the Word document's orientation is in Landscape mode?

I really appreciate any help you are able to give me.

Regards,

Steve

 

[Macropod]

Try:

Sub Excel_to_Word()
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc
    .PageSetup.Orientation = 1 'wdOrientLandscape
    Range("resfundwithdraw1").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter vbCr
    Range("resfundwithdraw2").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter vbCr
    Range("resfundwithdraw3").Copy
    .Range.Characters.Last.Paste
  End With
End With
End Sub

 

[bisel]

Hello,

I have been progressing along and making progress. Many thanks to Macropod with fantastic advice. Now I am able to create the three tables in Word from three individual ranges in Excel. I have also figured out how to apply some simple formatting to the Word document and to the tables. Here is the code I have developed with Macropod's help ...

Sub Excel_to_Word4()


Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim myTable As Table


With wdApp
  .Visible = True
  
  Set wdDoc = .Documents.Add
  
' Set up the Word document layout
  With wdDoc
    .PageSetup.Orientation = 1 'wdOrientLandscape
    .PageSetup.LeftMargin = InchesToPoints(0.5)
    .PageSetup.RightMargin = InchesToPoints(0.5)
    .PageSetup.TopMargin = InchesToPoints(0.5)
    .PageSetup.BottomMargin = InchesToPoints(0.5)
    
' Copy the first range from Excel to Word
    Range("resfundwithdraw1").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter vbCr ' Insert new line before next table
    
' Before copy and paste second table, insert column at end and format table
    Set myTable = wdDoc.Tables(1) ' Sets variable myTable to Table(1) in document
        With myTable
            .Columns.Add ' Adds a column to the right of the table preparing for last paste
            .AutoFitBehavior wdAutoFitWindow 'Autofit this table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With 'myTable
            
' Copy the second range from Excel to Word
    Range("resfundwithdraw2").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter vbCr ' Insert new line before next table
    
' Before copy and paste third table, insert column at end and format table
    Set myTable = wdDoc.Tables(2) ' Sets variable myTable to Table(2) in document
        With myTable
            .Columns.Add ' Adds a column to the right of the table preparing for last paste
            .AutoFitBehavior wdAutoFitWindow 'Autofit this table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With 'myTable
    
' Copy the third range from Excel to Word
    Range("resfundwithdraw3").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter vbCr ' Insert new line
    
' Insert column at end and format table
    Set myTable = wdDoc.Tables(3) ' Sets variable myTable to Table(3) in document
        With myTable
            .Columns.Add ' Adds a column to the right of the table preparing for last paste
            .AutoFitBehavior wdAutoFitWindow 'Autofit this table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With 'myTable
  
  End With 'wdDoc
End With ' wdApp


End Sub

I am now stuck trying to insert a page break after the first and second tables. Any help is much appreciated.

Regards,

Steve

 

[Macropod]

It would really be useful if you said what you're actually trying to do. Change:
.Range.InsertAfter vbCr
to:
.Range.InsertAfter Chr(12)

Furthermore, you could avoid all of:

    .PageSetup.Orientation = 1 'wdOrientLandscape
    .PageSetup.LeftMargin = InchesToPoints(0.5)
    .PageSetup.RightMargin = InchesToPoints(0.5)
    .PageSetup.TopMargin = InchesToPoints(0.5)
    .PageSetup.BottomMargin = InchesToPoints(0.5)

if you created a Word template with the required setup and called that via .Documents.Add.

 

[bisel]

Thank you so much, Macropod. At first, I though I wanted a vbCr. Only after seeing results that I changed to a new page. Thanks for showing me the way.

I thought about the Word Template, but this code will be used in a workbook that goes to other users and there is little reason to assume that they would have the correct template ... so, had to do the page setup.

Thanks again.

Steve

 

[Macropod]

I thought about the Word Template, but this code will be used in a workbook that goes to other users and there is little reason to assume that they would have the correct template ... so, had to do the page setup.

In that case, you'd better define the page size, etc., too. Do note that, when you do it without a template (which you could distribute with the workbook), you're at the mercy of whatever changes the users have made to the Styles defined in their Normal.dotm files. Note also that, provided your template is stored in a location the users can all access, there is no need to distribute it as you can point the code to that location.

 

[bisel]

Thanks for that advice, Paul.

Just an FYI ... the workbook that I am distributing is a financial analysis application. This macro I am building will copy three tables of asset components and place them in a Word doc so the user can have an external copy (external to the Excel workbook). This may not be necessary for every one as it depends on the number of assets they have. If the number is small, the user can easily copy and paste the small table into a PowerPoint slide. But if the number of assets is large, they will not fit on a PowePoint slide so the external document might be handy for the user to create and then reference in his/her PowerPoint slide deck.

Thanks for all your help.

Regards,

Steve