VBA to import excel data in word document

[zyrtec]

Edit: maybe I should have posted this under "Word vba" instead of "Excel vba", but I don't know how to remove / move this thread...

Hi,

I'm rather new to vba and I've been trying to make a MS Word macro to import data from Excel into an existing Word document. The idea is to first prompt the user to select an excel file, so the macro can copy all of the cells (of each sheet in the workbook) into the active Word document (paste special as enhanced metafile).

I already have some code (see below). The problem is that when the first sheet is copied, the bookmark is replaced by the image. Hence, the second copy-paste can't find a bookmark and gives an error code 5941. I also have to figure out how to get an hard return between the images.

I think the solution is to paste the data before the bookmark. That way, the bookmark isn 't replaced and the macro can be executed again (for other excel files). I just can 't figure out how to do this...

Also, sometimes when I run the macro again I get an errorcode 91 (no idea how this happens, I thought all Objects are set fine).

Thanks for the help/advice!



Sub ImportTablesFromExcel()

' path word file
Dim fullpathWord As String
fullpathWord = ActiveDocument.FullName

' select excel file
Dim fullpathExcel As String
With Application.FileDialog(msoFileDialogOpen)
.Show
If .SelectedItems.Count = 1 Then
fullpathExcel = .SelectedItems(1)
Else
MsgBox ("Please select only one file.")
Exit Sub
End If
End With

' Word objects
Dim WDapp As Word.Application
Dim WDdoc As Word.Document
Set WDapp = GetObject(, "Word.Application")
Set WDdoc = WDapp.ActiveDocument

' Excel objects
Dim appXL As Excel.Application
Dim wbXL As Excel.Workbook
Dim sht As Worksheet
Set appXL = New Excel.Application
Set wbXL = appXL.Workbooks.Open(fullpathExcel)
appXL.Visible = True
wbXL.Activate

' Copy cells of each sheet into word doc
For Each Worksheet In ActiveWorkbook.Worksheets
Cells.Copy
WDapp.Visible = True
WDdoc.Bookmarks("Table").Range.PasteSpecial DataType:=wdPasteEnhancedMetafile
Next

Set WDapp = Nothing
Set WDdoc = Nothing
Set appXL = Nothing
Set wbXL = Nothing

End Sub

[zyrtec]

After a good night's sleep, I have altered the code and the bookmark (now the bookmark doesn 't include the first letter of the phrase, otherwise the pasted images were embedded in the bookmark).

The only error i get is "error 91", when I run the macro again to import other data (from another file). Any help or advice please?



Sub ImportTablesFromExcel()

' path word file
Dim fullpathWord As String
fullpathWord = ActiveDocument.FullName

' select excel file
Dim fullpathExcel As String
With Application.FileDialog(msoFileDialogOpen)
.Show
If .SelectedItems.Count = 1 Then
fullpathExcel = .SelectedItems(1)
Else
MsgBox ("Please select only one file.")
Exit Sub
End If
End With

' Word objects
Dim WDapp As Word.Application
Dim WDdoc As Word.Document
Set WDapp = GetObject(, "Word.Application")
Set WDdoc = WDapp.ActiveDocument

' Excel objects
Dim appXL As Excel.Application
Dim wbXL As Excel.Workbook
Set appXL = New Excel.Application
Set wbXL = appXL.Workbooks.Open(fullpathExcel)
appXL.Visible = True
wbXL.Activate

' Copy cells of each sheet into word doc
For Each Worksheet In ActiveWorkbook.Worksheets
wbXL.Unprotect
Cells.Interior.Pattern = xlSolid
Cells.Copy
'Cells.Interior.Pattern = xlNone
WDapp.Visible = True
WDdoc.Bookmarks("Table").Select
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.TypeParagraph
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.PasteSpecial DataType:=wdPasteEnhancedMetafile, Placement:=wdInLine, DisplayAsIcon:=False
wbXL.Protect
Next
appXL.Quit
Set WDapp = Nothing
Set WDdoc = Nothing
Set appXL = Nothing
Set wbXL = Nothing

End Sub