1 Attachment(s)
Excel VBA- Disable Automatically resize option when converting to Word
I am using userforms to transfer data into worksheet, then converting it to word document. I created bunch of tables to be fill in userform textboxes. Since some of the comments could be very long, I set those tables as wrap text and autofit row height. While some of the tables are fitting word page, some of them are going beyond of page and some of the borders are going below page without proper format.
Attachment 22405
1st and 3rd columns have been shrink. I can solve this problem on word as disabling the Automatically resize to fit contents option. However, since I have 10 tables, I need to disable this option from Excel vba.
How could I fit those borders into word document without overflow to the other pages and without shrinking.
Code:
Sub TestingMacAndWin1()
Dim appWD As Object
Dim wddoc As Object
On Error Resume Next
Set appWD = GetObject(, "Word.application") 'gives error 429 if Word is not open
If Err = 429 Then
Set appWD = CreateObject("Word.application") 'creates a Word application
Err.Clear
End If
Set wddoc = appWD.Documents.Add
appWD.Visible = True
With appWD.ActiveDocument.PageSetup
.Orientation = 1
.Content.Style = .Styles("No Spacing")
.TopMargin = appWD.InchesToPoints(0.3)
.BottomMargin = appWD.InchesToPoints(0.3)
.LeftMargin = appWD.InchesToPoints(0.3)
.RightMargin = appWD.InchesToPoints(0.3)
.InsertBreak Type:=0
End With
Sheets("Report").Range("N16").CurrentRegion.Copy
appWD.Selection.Paste
With appWD.Selection
.AllowAutoFit = False
.Collapse Direction:=0
End With
appWD.Activate
End Sub
