Copy data from Excel to Word

[sofas]

Welcome. I have a database in an Excel workbook called Reports whose data range is from column A to M. I manually filter it by the employee name in column T. What I am looking for is whether there is a possibility to create a code that enables me to create a WORD file with the same path as the workbook and copy only the columns that appear on it, starting with cell A6. I tried searching, but unfortunately I did not find any solution similar to my request. I don't know if there is a possibility to do that or not

 

[NateSC]

I have done this with a template Word file with bookmarks. Some of the bookmarks took single pieces of data, but the last thing was to build an Excel sheet and then copy it to the last bookmark in the Word file. Below will be some of the pieces of what you need, but it will need to be modified to match exactly what you want. I hope this helps. I don't show variable declarations. Let me know if you need those.

' Make some variables for file name and path
        myWord = "Form-Template.docx"
        myPath = ActiveWorkbook.Path
' Create an instance of Word, Set the references to the specific files
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = True
        Set docDest = objWord.Documents.Open(myPath & "\" & myWord)
        
        ' Use the report form to generate the report - added to worksheet with the name myWSName
' After I create the worksheet with the data I want, I find the last row and last column, and then copy
' everything.  If you only want to start at A6, change ".Range(.Cells(1,1)," to ".Range(.Cells(6,1),"
        With myWB.Sheets(myWSName)
            tmpRows = .Cells(.Rows.Count, 1).End(xlUp).Row
            tmpCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
            .Range(.Cells(1, 1), .Cells(tmpRows, tmpCols)).Copy
        End With
' Paste the copied data into Word at the bookmark "empHistory"
        docDest.bookmarks("empHistory").Range.PasteExcelTable _
                               LinkedToExcel:=False, _
                               WordFormatting:=False, _
                               RTF:=False

' The rest of this is creating a new name, saving the file, printing the Word file, Closing the file
' closing Word, and then clearing out "objWord" so I can do this again if needed
        myNewName = myPath & "My File Name" & Format(txtDate.Value, "dd-mm-yy") & ".docx"
        docDest.SaveAs (myNewName)
        
        docDest.PrintOut
        
        docDest.Close
        objWord.Quit
        Set objWord = Nothing

 

[sofas]

I have done this with a template Word file with bookmarks. Some of the bookmarks took single pieces of data, but the last thing was to build an Excel sheet and then copy it to the last bookmark in the Word file. Below will be some of the pieces of what you need, but it will need to be modified to match exactly what you want. I hope this helps. I don't show variable declarations. Let me know if you need those.

' Make some variables for file name and path
        myWord = "Form-Template.docx"
        myPath = ActiveWorkbook.Path
' Create an instance of Word, Set the references to the specific files
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = True
        Set docDest = objWord.Documents.Open(myPath & "\" & myWord)
       
        ' Use the report form to generate the report - added to worksheet with the name myWSName
' After I create the worksheet with the data I want, I find the last row and last column, and then copy
' everything.  If you only want to start at A6, change ".Range(.Cells(1,1)," to ".Range(.Cells(6,1),"
        With myWB.Sheets(myWSName)
            tmpRows = .Cells(.Rows.Count, 1).End(xlUp).Row
            tmpCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
            .Range(.Cells(1, 1), .Cells(tmpRows, tmpCols)).Copy
        End With
' Paste the copied data into Word at the bookmark "empHistory"
        docDest.bookmarks("empHistory").Range.PasteExcelTable _
                               LinkedToExcel:=False, _
                               WordFormatting:=False, _
                               RTF:=False

' The rest of this is creating a new name, saving the file, printing the Word file, Closing the file
' closing Word, and then clearing out "objWord" so I can do this again if needed
        myNewName = myPath & "My File Name" & Format(txtDate.Value, "dd-mm-yy") & ".docx"
        docDest.SaveAs (myNewName)
       
        docDest.PrintOut
       
        docDest.Close
        objWord.Quit
        Set objWord = Nothing

Thank you, but unfortunately it did not work for me. After trying the following code, I obtained 90 percent of what was required. The problem I face is the formatting of the sheet after it is created. It must be displayed in OrientPortrait so that I can see all the columns.

Public Sub ExcelToWord()
    Dim objWd As Word.Application
    Set objWd = CreateObject("word.application")
    objWd.Visible = True
    Dim WS As Worksheet
    Set WS = ActiveSheet
    Dim objDoc As Word.Document
    
   WS.Range("A6:M6", WS.Range("a" & _
                Rows.Count).End(xlUp)).Copy

Set objDoc = objWd.Documents.Add
objWd.Selection.Paste


'.Selection.PageSetup.Orientation = wdOrientPortrait

    objDoc.SaveAs ThisWorkbook.path & "\" & WS.Name & ".docx"
    
    
          objDoc.Close
       objWd.Quit
    Set objWd = Nothing
   Set objDoc = Nothing
        
End Sub

 

[NateSC]

Try pasting with the following:

objWd.Selection.PasteExcelTable _
    LinkedToExcel:=False, _
    WordFormatting:=True, _
    RTF:=False

 

[sofas]

Try pasting with the following:

objWd.Selection.PasteExcelTable _
    LinkedToExcel:=False, _
    WordFormatting:=True, _
    RTF:=False

Thank you. Your suggestion helped me somewhat, but I am still suffering from viewing the Word document. Can I add within the code to display it in A3 size in order to expand the vision?

 

[NateSC]

I haven't messed with paper size before, but it shouldn't be too difficult. Are you allowed to display in Landscape?
Other possibilities:
-reduce the font size in the table
-reduce the overall width of the table

Can you manually paste the data into Word and make it fit?

 

[sofas]

I haven't messed with paper size before, but it shouldn't be too difficult. Are you allowed to display in Landscape?
Other possibilities:
-reduce the font size in the table
-reduce the overall width of the table

Can you manually paste the data into Word and make it fit?

I haven't messed with paper size before, but it shouldn't be too difficult. Are you allowed to display in Landscape?
Other possibilities:
-reduce the font size in the table
-reduce the overall width of the table

Can you manually paste the data into Word and make it fit?

Thank you again. I thought of something like this and fortunately the idea worked. I appreciate your help and suggestions @NateSC

objWd.ActiveDocument.PageSetup.Orientation = wdOrientLandscape
objWd.ActiveDocument.PageSetup.PaperSize = WdPaperSize.wdPaperA3

 

[NateSC]

Great. I was running out of ideas.