CODEWITHSUNDEEP
vbams-word
ahumble_bumblebee
ahumble_bumblebee

Reputation: 37

copy and paste named ranges from excel workbooks into one word document

I am programming a command button in word that copies and pastes (as a picture) a named range from multiple excel spreadsheets into the same (open) word document. With some help from here, I got it to work for a single excel file, but when I try to modify the code for multiple selection, all it does is paste the same range (i.e. from one workbook) multiple times. Also, there must be a more elegant (read: faster) way than to keep opening and closing excel to copy the range...

Private Sub CommandButton1_Click()
  Dim objExcel As New Excel.Application, exWb As Workbook
    With Application.FileDialog(msoFileDialogFilePicker)
      .Title = "Select Risk Assessment Files"
      .Filters.Add "Macro-Enabled Excel Files", "*.xlsm"
      .AllowMultiSelect = True

    If .Show <> -1 Then Exit Sub
    For Each vrtSelectedItem In .SelectedItems
      Set exWb = objExcel.Workbooks.Open(.SelectedItems(1))
      '*** Use the values from excel here***
      exWb.Sheets("Risk Assessment").Range("CRA").CopyPicture_
         Appearance:=xlScreen, Format:=xlPicture

      '*** Close the opened Excel file
      exWb.Close Savechanges:=False
      Set exWb = Nothing
      Selection.EndKey Unit:=wdStory
      Selection.Paste

    Next vrtSelectedItem
  End With
  Selection.GoTo What:=wdGoToBookmark, Name:="insertbutton"
  MsgBox ("Done")
End Sub

Thanks for your help...

Upvotes: 1

Views: 224

Answers (1)

A.S.H
A.S.H

Reputation: 29352

You're opening the same file every time

Set exWb = objExcel.Workbooks.Open(.SelectedItems(1))

That's why all it does is paste the same range (i.e. from one workbook) multiple times. Change to:

Set exWb = objExcel.Workbooks.Open(vrtSelectedItem)

And you'll get a different workbook every time.

Regarding the question about a "more elegant way", you need to open and close each and every workbook to copy from, so keep it that way.

Upvotes: 1

Related Questions