hades777
01-14-2020, 12:29 PM
Hello guys, I have been trying to resize the excel tables I copy as image to a bookmark in a word document but have not been able. The tables are copied as expected but I am trying to make it fit better to the document. Some are copied too small so we cannot see well. I tried to find a code that would make it more fit to the document but no chance. Can anyone help me out? I will paste the code below :
Sub CopyTables()
Application.ScreenUpdating = False
Application.StatusBar = "Creating files..."
Dim wd, wd2, wd3, wd4, wd5, wd6, wd7, wd8 As Word.Application
Dim doc As Word.Document
Dim templ As String
Dim srcWkBkName As String
Dim srcWkBk As Workbook
Dim srcRg As String
Dim destRg As Word.Range
Dim bmks As Variant
Dim k As Integer
Dim rng As Range
Dim rngarray, rngarray_bm, rngarray2, rngarray2_bm, rngarray3, rngarray3_bm, rngarray4, rngarray4_bm, rngarray5, rngarray5_bm, rngarray6, rngarray6_bm, rngarray7, rngarray7_bm, rngarray8, rngarray8_bm As Variant
Dim img As Shape, cht As Chart
rngarray = Array("Section_2.1.0.1", "Section_2.1.0.2" _
, "Section_2.2.0.1", "Section_2.2.3.0.1", "Section_2.3a.0.1" _
, "Section_2.3a.0.2", "Section_2.3b.0.1", "Section_2.3b.0.2" _
, "Section_2.4a.0.1", "Section_2.4a.0.2", "Section_2.4a.0.3" _
, "Section_2.4a.0.4", "Section_2.4a.0.5", "Section_2.4a.0.6" _
, "Section_2.4b.0.1")
rngarray_bm = Array("bm1", "bm2", "bm3", "bm4", "bm5", "bm6", "bm7", "bm8", "bm9", "bm10", "bm11", "bm12", "bm13", "bm14", "bm15")
Set wd = CreateObject("Word.Application")
wd.Visible = True
templ = "C:\AA Report - Section 2 - templ.doc"
Set doc = wd.Documents.Add(Template:=templ)
Set srcWkBk = ThisWorkbook
'For k = 0 To UBound(rngarray)
For k = 0 To CInt(UBound(rngarray))
srcWkBk.Sheets("Section 2").Range(rngarray(k)).CopyPicture
'rngarray(k).CopyPicture
Set destRg = doc.bookmarks(rngarray_bm(k)).Range
destRg.Paste
'destRg.InlineShapes(1).ScaleHeight = 100
'destRg.InlineShapes(1).ScaleWidth = 70
'Selection.Width = 500
'Selection.Height = 500
Next
doc.SaveAs Filename:="C:\AA Report - Section 2.doc"
doc.Close
wd.Visible = False
End sub
The scale parameters which are commented do not work well because they just push the image further to the right and it goes farther than the margins. Any help please?
Sub CopyTables()
Application.ScreenUpdating = False
Application.StatusBar = "Creating files..."
Dim wd, wd2, wd3, wd4, wd5, wd6, wd7, wd8 As Word.Application
Dim doc As Word.Document
Dim templ As String
Dim srcWkBkName As String
Dim srcWkBk As Workbook
Dim srcRg As String
Dim destRg As Word.Range
Dim bmks As Variant
Dim k As Integer
Dim rng As Range
Dim rngarray, rngarray_bm, rngarray2, rngarray2_bm, rngarray3, rngarray3_bm, rngarray4, rngarray4_bm, rngarray5, rngarray5_bm, rngarray6, rngarray6_bm, rngarray7, rngarray7_bm, rngarray8, rngarray8_bm As Variant
Dim img As Shape, cht As Chart
rngarray = Array("Section_2.1.0.1", "Section_2.1.0.2" _
, "Section_2.2.0.1", "Section_2.2.3.0.1", "Section_2.3a.0.1" _
, "Section_2.3a.0.2", "Section_2.3b.0.1", "Section_2.3b.0.2" _
, "Section_2.4a.0.1", "Section_2.4a.0.2", "Section_2.4a.0.3" _
, "Section_2.4a.0.4", "Section_2.4a.0.5", "Section_2.4a.0.6" _
, "Section_2.4b.0.1")
rngarray_bm = Array("bm1", "bm2", "bm3", "bm4", "bm5", "bm6", "bm7", "bm8", "bm9", "bm10", "bm11", "bm12", "bm13", "bm14", "bm15")
Set wd = CreateObject("Word.Application")
wd.Visible = True
templ = "C:\AA Report - Section 2 - templ.doc"
Set doc = wd.Documents.Add(Template:=templ)
Set srcWkBk = ThisWorkbook
'For k = 0 To UBound(rngarray)
For k = 0 To CInt(UBound(rngarray))
srcWkBk.Sheets("Section 2").Range(rngarray(k)).CopyPicture
'rngarray(k).CopyPicture
Set destRg = doc.bookmarks(rngarray_bm(k)).Range
destRg.Paste
'destRg.InlineShapes(1).ScaleHeight = 100
'destRg.InlineShapes(1).ScaleWidth = 70
'Selection.Width = 500
'Selection.Height = 500
Next
doc.SaveAs Filename:="C:\AA Report - Section 2.doc"
doc.Close
wd.Visible = False
End sub
The scale parameters which are commented do not work well because they just push the image further to the right and it goes farther than the margins. Any help please?