How to copy excel range from a sheet and place it into a specific place it into a specific place in word using word vba

Question

I am trying to auto-populate a word document using the information in excel sheet. I want the user to be able to pick a desired excel file and want my macro to copy a specific range from different sheets for excel file and paste it into the specific area in word. So far I am able to open an excel file of my choice but not sure how the selection of range and pasting into specific location would work. Also, I am doing all this using word VBA and would also want to use word VBA to copy paste excel range. I am trying to copy cell C25 from sheet named Main and table ranged A5 to C28 from sheet named summary. Cell C25 is a name so I want to place it in a word doc stating Dear Name and post table after the line please see the summary below:.This is what I have so far that allows me to pick excel file and open it.

Sub Macro1_FileOpen()
'
' Macro1_FileOpen Macro
'
'
'Dim FilePath As Object
'Dim dSource As String

'With Me.Application.FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen)

   ' .AllowMultiSelect = True
    '.Filters.Clear
    '.Filters.Add "Excel Files", "*.xls;*.xlw"
   ' .Filters.Add "All Files", "*.*"

   ' If .Show = True Then
    '    .Execute()
    'End If
'End With

Dim xlApp As Object
Dim xlBook As Object
Dim dSource As String
Dim ExcelWasNotRunning As Boolean
Dim MyDOc As Word.Document
Dim fd As FileDialog




Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .Title = "Select the file that you wish to use."
    .InitialFileName = strFolder
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls?"
    .AllowMultiSelect = False
    If .Show = -1 Then
        dSource = .SelectedItems(1)
    Else
        MsgBox "You cancelled the operation"
        Exit Sub
    End If
End With
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
    ExcelWasNotRunning = True
    Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
With xlApp
    Set xlBook = .Workbooks.Open(dSource)
    MsgBox "The workbook" & vbCr & dSource & vbCr & "is open!"

    'Insert code to do what you need to do with the workbook here



    Set xlBook = Nothing
End With
If ExcelWasNotRunning Then
    xlApp.Quit
End If
Set xlBook = Nothing
Set xlApp = Nothing
Set fd = Nothing
End Sub

Variatus Variatus · Accepted Answer · 2017-08-24T18:29:11

This looks like a case of tired legs after the first mile of the Marathon :-). You will just have to continue plodding on the way you started. But avoid declaring everything as Object. xlApp is an Excel.Application. xlBook is an Excel.Workbook. MyDoc is a Word.Document, but you don't need to specify Word while you are running code from Word.

You will have to set MyDoc however, such as Set MyDoc = ActiveDocument. Declare Dim Ws As Excel.Worksheet and Set Ws = xlBook.Worksheets("Main") You will need a string to take text from xlBook. So Dim Tmp As String. Then you can say Tmp = Ws.Cells(25, "C").Value, look for the place in MyDoc and insert it there.

Then you can declare Dim Tbl As Table and

Set Ws = xlBook.Worksheets("Summary")
With Ws
    Set Tbl = .Range(.Cells(5, "A"), .Cells(28, "C"))
End With

Actually, I am by no means sure that this will work - assigning an Excel range to a Word table - but if it doesn't you might try simply copying the Excel.Range directly into your Word document, or try recording the steps while you execute them, or ask a specific question here about how to copy an Excel range to a Word document. The point I am trying to make here is simply that there is a lot of nitty-gritty work to be done on your project before you reach any problem which can't be explained with feeling tired. Keep up the good work, man. And good luck! (Please try to avoid anything that looks like a follow-up question, lol:)

How to copy excel range from a sheet and place it into a specific place it into a specific place in word using word vba

1 Answers