zyrtec
08-06-2015, 12:47 PM
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
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