Import to Word Excel Worksheet's as images

[Pedro Serra]

How can I, automatically import to different points of a word document,
images of a specific range of different worksheets in an excel document?

 

[Ed]

Hi, Pedro. How deeply do you want to automate this? If you want to be at
any particular point in a Word doc and simply click a button and have Word
and Excel automatically know what you want imported, that's going to take
some doing. On the other hand, could you get by with putting a button on
your Excel header mapped to the Copy As Picture command, then ALT+Tab to
Excel, select range and hit button, ALT+Tab to Word and paste? What are you
working with and what do you want to accomplish?

Ed

 

[Pedro Serra]

I’m working with several worksheets an each one of them containing a table
that I would like to import to an ms word document.

My idea was/is to import all the several xls tables to the ms word all at
once.

If I can specified the places where the data is going to be placed in the ms
word (one location to one xls table) that will be great … but I’ll will be
very satisfied if I could import the data all at once no matter the places
that will be putted in the ms word document.

Do u think u can help me achieving this?

 

[Ed]

Is this going to be a "master" Word document - that is, it's like a form
with blanks for the tables, and it's the same doc that gets pulled up and
filled in every time you need to run this? The tables will always go in the
same place every time you fill in this report?

Are the Excel tables always going to be on the same sheets in the same
place, spanning the same range area, and always imported into the Word doc
in the same order?

If yes to both, this is actually pretty easy.

If you have to redo the Word doc every time so the tables are always in a
different place, or the tables in Excel are not always the same size in the
same place, it gets more difficult. Now you are going to have to decide
whether you learn to do lots of coding to account for every possible
variation, or allow for lots of user (that's you!) intervention - like stop,
click here, continue, stop select this, continue, stop, select this, etc.

Ed

 

[Pedro Serra]

Ed,

Yes to both!

Do u think u can help me?

Thanks in advanced,

 

[Ed]

Pedro:

I will write a macro that will go into your "master" document's ThisDocument
module as an AutoOpen procedure. It will run every time the doc opens, and
save the changed file as another Word doc.

I have online access for only about 8 more hours, then I'm off for several
days. If you can get back within that time, I would like to know
(a) how many Excel tables are we talking about?
(b) does each one cover the same area on each worksheet (does each table
cover, say, A1:F8)?

Ed

 

[Ed]

Pedro:

This macro is NOT an "AutoOpen". Create your "master" document, create a
new module in the VBE, and paste this code into it. You will also need to
set a reference (Tools >> Reference) to the Excel library. Macro security
must be at least medium.

This macro was written with the following assumptions:
(1) The "master" doc has bookmarks at every place you want an Excel table
(2) All bookmarks are sequentially numbered
(3) Each Excel table is on a separate worksheet
(4) The table from Sheet1 goes to Bookmark1, Sheet2 to Bookmark2, etc.

All of this can be adjusted.

I tested this with no Excel files open, and no other Word docs open except
my Test doc. I can not guarantee this will work if other files are open.
Note that if you use Outlook and Word is your email editor, there may be
strange things happen. More than like not, but there are always those times
when "something" happens - usually when you're up against a deadline!

Ed

Sub GetAllExcel()

' This macro requires a reference to the
' Microsoft Excel library

' This macro is designed to open an Excel workbook,
' copy ranges from each worksheet using Copy As Picture,
' and paste these pictures into a Word document
' at specified bookmarks.
'
' Macro created 18 May 2006 by Ed Millis

Dim objDoc As Document
Dim objBkm As Bookmark
Dim objXL As New Excel.Application
Dim objWkb As Excel.Workbook
Dim objWks As Excel.Worksheet
Dim rngXL As Excel.Range
Dim x As Long
Dim strBkm As String
Dim strSht As String

Set objDoc = ActiveDocument

' Get Excel file
Set objXL = New Excel.Application
objXL.Visible = True
objXL.Dialogs(xlDialogOpen).Show
Set objWkb = objXL.ActiveWorkbook

' Loop through all bookmarks
' and populate with Excel ranges
' This macro assumes the Excel range to be
' inserted in the Word document at Bookmark1
' is in the Excel workbook on Sheet1, and so forth
For x = 1 To objDoc.Bookmarks.Count
' Set names of bookmark to populate
' and sheet to get table from
strBkm = "Bookmark" & x
strSht = "Sheet" & x
' Get table
Set objWks = objWkb.Sheets(strSht)
Set rngXL = objWks.Range("A1:F10")
rngXL.CopyPicture
' Find bookmark and paste
objDoc.Bookmarks(strBkm).Range.Paste
Next x
Stop

' Close workbook, leave Excel
objWkb.Close
objXL.Quit

' Save document with tables
objDoc.SaveAs objDoc.Path & "\Excel_Tables_" & Format(Now, "dd-mmm-yyyy") &
".doc"

' Clean up
Set objWks = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set objDoc = Nothing
End Sub

 

[Pedro Serra]

a) 30 tables

b) No, the tables have diferent ranges.

Thanks again,

 

[Ed]

Okay, then - here's the code modified to set the range to whichever sheet
comes up. You will have to go into the code and modify
Set rngXL = objWks.Range("A1:F10")
to reflect the cell range settings particular to each sheet.

Hope this works for you.
Ed

Sub GetAllExcel()

' This macro requires a reference to the
' Microsoft Excel library

' This macro is designed to open an Excel workbook,
' copy ranges from each worksheet using Copy As Picture,
' and paste these pictures into a Word document
' at specified bookmarks.
'
' Macro created 18 May 2006 by Ed Millis

Dim objDoc As Document
Dim objBkm As Bookmark
Dim objXL As New Excel.Application
Dim objWkb As Excel.Workbook
Dim objWks As Excel.Worksheet
Dim rngXL As Excel.Range
Dim x As Long
Dim strBkm As String
Dim strSht As String

Set objDoc = ActiveDocument

' Get Excel file
Set objXL = New Excel.Application
objXL.Visible = True
objXL.Dialogs(xlDialogOpen).Show
Set objWkb = objXL.ActiveWorkbook

' Loop through all bookmarks
' and populate with Excel ranges
' This macro assumes the Excel range to be
' inserted in the Word document at Bookmark1
' is in the Excel workbook on Sheet1, and so forth
For x = 1 To objDoc.Bookmarks.Count
' Set names of bookmark to populate
' and sheet to get table from
strBkm = "Bookmark" & x
strSht = "Sheet" & x
' Get table
Set objWks = objWkb.Sheets(strSht)

' Set range on worksheet
Select Case x
Case 1
Set rngXL = objWks.Range("A1:F10")
Case 2
Set rngXL = objWks.Range("A1:F10")
Case 3
Set rngXL = objWks.Range("A1:F10")
Case 4
Set rngXL = objWks.Range("A1:F10")
Case 5
Set rngXL = objWks.Range("A1:F10")
Case 6
Set rngXL = objWks.Range("A1:F10")
Case 7
Set rngXL = objWks.Range("A1:F10")
Case 8
Set rngXL = objWks.Range("A1:F10")
Case 9
Set rngXL = objWks.Range("A1:F10")
Case 10
Set rngXL = objWks.Range("A1:F10")
Case 11
Set rngXL = objWks.Range("A1:F10")
Case 12
Set rngXL = objWks.Range("A1:F10")
Case 13
Set rngXL = objWks.Range("A1:F10")
Case 14
Set rngXL = objWks.Range("A1:F10")
Case 15
Set rngXL = objWks.Range("A1:F10")
Case 16
Set rngXL = objWks.Range("A1:F10")
Case 17
Set rngXL = objWks.Range("A1:F10")
Case 18
Set rngXL = objWks.Range("A1:F10")
Case 19
Set rngXL = objWks.Range("A1:F10")
Case 20
Set rngXL = objWks.Range("A1:F10")
Case 21
Set rngXL = objWks.Range("A1:F10")
Case 22
Set rngXL = objWks.Range("A1:F10")
Case 23
Set rngXL = objWks.Range("A1:F10")
Case 24
Set rngXL = objWks.Range("A1:F10")
Case 25
Set rngXL = objWks.Range("A1:F10")
Case 26
Set rngXL = objWks.Range("A1:F10")
Case 27
Set rngXL = objWks.Range("A1:F10")
Case 28
Set rngXL = objWks.Range("A1:F10")
Case 29
Set rngXL = objWks.Range("A1:F10")
Case 30
Set rngXL = objWks.Range("A1:F10")
End Select

rngXL.CopyPicture
' Find bookmark and paste
objDoc.Bookmarks(strBkm).Range.Paste
Next x
Stop

' Close workbook, leave Excel
objWkb.Close
objXL.Quit

' Save document with tables
objDoc.SaveAs objDoc.Path & "\Excel_Tables_" & Format(Now, "dd-mmm-yyyy") &
".doc"

' Clean up
Set objWks = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set objDoc = Nothing

End Sub

 

[Pedro Serra]

Thanks a lot ED!

Great Code ...

Okay, then - here's the code modified to set the range to whichever sheet
comes up. You will have to go into the code and modify
Set rngXL = objWks.Range("A1:F10")
to reflect the cell range settings particular to each sheet.

Hope this works for you.
Ed

Sub GetAllExcel()

' This macro requires a reference to the
' Microsoft Excel library

' This macro is designed to open an Excel workbook,
' copy ranges from each worksheet using Copy As Picture,
' and paste these pictures into a Word document
' at specified bookmarks.
'
' Macro created 18 May 2006 by Ed Millis

Dim objDoc As Document
Dim objBkm As Bookmark
Dim objXL As New Excel.Application
Dim objWkb As Excel.Workbook
Dim objWks As Excel.Worksheet
Dim rngXL As Excel.Range
Dim x As Long
Dim strBkm As String
Dim strSht As String

Set objDoc = ActiveDocument

' Get Excel file
Set objXL = New Excel.Application
objXL.Visible = True
objXL.Dialogs(xlDialogOpen).Show
Set objWkb = objXL.ActiveWorkbook

' Loop through all bookmarks
' and populate with Excel ranges
' This macro assumes the Excel range to be
' inserted in the Word document at Bookmark1
' is in the Excel workbook on Sheet1, and so forth
For x = 1 To objDoc.Bookmarks.Count
' Set names of bookmark to populate
' and sheet to get table from
strBkm = "Bookmark" & x
strSht = "Sheet" & x
' Get table
Set objWks = objWkb.Sheets(strSht)

' Set range on worksheet
Select Case x
Case 1
Set rngXL = objWks.Range("A1:F10")
Case 2
Set rngXL = objWks.Range("A1:F10")
Case 3
Set rngXL = objWks.Range("A1:F10")
Case 4
Set rngXL = objWks.Range("A1:F10")
Case 5
Set rngXL = objWks.Range("A1:F10")
Case 6
Set rngXL = objWks.Range("A1:F10")
Case 7
Set rngXL = objWks.Range("A1:F10")
Case 8
Set rngXL = objWks.Range("A1:F10")
Case 9
Set rngXL = objWks.Range("A1:F10")
Case 10
Set rngXL = objWks.Range("A1:F10")
Case 11
Set rngXL = objWks.Range("A1:F10")
Case 12
Set rngXL = objWks.Range("A1:F10")
Case 13
Set rngXL = objWks.Range("A1:F10")
Case 14
Set rngXL = objWks.Range("A1:F10")
Case 15
Set rngXL = objWks.Range("A1:F10")
Case 16
Set rngXL = objWks.Range("A1:F10")
Case 17
Set rngXL = objWks.Range("A1:F10")
Case 18
Set rngXL = objWks.Range("A1:F10")
Case 19
Set rngXL = objWks.Range("A1:F10")
Case 20
Set rngXL = objWks.Range("A1:F10")
Case 21
Set rngXL = objWks.Range("A1:F10")
Case 22
Set rngXL = objWks.Range("A1:F10")
Case 23
Set rngXL = objWks.Range("A1:F10")
Case 24
Set rngXL = objWks.Range("A1:F10")
Case 25
Set rngXL = objWks.Range("A1:F10")
Case 26
Set rngXL = objWks.Range("A1:F10")
Case 27
Set rngXL = objWks.Range("A1:F10")
Case 28
Set rngXL = objWks.Range("A1:F10")
Case 29
Set rngXL = objWks.Range("A1:F10")
Case 30
Set rngXL = objWks.Range("A1:F10")
End Select

rngXL.CopyPicture
' Find bookmark and paste
objDoc.Bookmarks(strBkm).Range.Paste
Next x
Stop

' Close workbook, leave Excel
objWkb.Close
objXL.Quit

' Save document with tables
objDoc.SaveAs objDoc.Path & "\Excel_Tables_" & Format(Now, "dd-mmm-yyyy") &
".doc"

' Clean up
Set objWks = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set objDoc = Nothing

End Sub

 

[Pedro Serra]

Can u please send me your xls and word test documents to my e-mail?

(e-mail address removed)

This will help me a lot!

Thanks in advanced

 

[Depechez]

HI,

I have a list of words on Word --like a dictionary. I want to inscribe them
into an Excel sheet all at onece --not one by one. Is there a way? Thanks.

 

[Jean-Guy Marcil]

Depechez was telling us:
Depechez nous racontait que :

HI,

I have a list of words on Word --like a dictionary. I want to
inscribe them into an Excel sheet all at onece --not one by one. Is
there a way? Thanks.

How is this list of word laid out on the page?

This is a vba group (Visual Basic for Application, or Programming in Word),
is this a coding question or you want to do it manually?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[rexie3]

Hello Ed,

My problem is a bit less complicated. I hope you can help me. I'm creating
a Form in Word. At some point in the document I have a Command Button. I
want the user to hit the button and the Excel sheet they saved would be
imported.

The name and location of the workbook will always be the same. How do get
the button to do this? I've been reading around and I'm not getting anywhere.

The XL file is called CoCtable temp.xls.

Thank you.