I have a range in excel that I am exporting to a word document and then saving the document. The problem I have is I than have to open the file and manually change the margins to Narrow and change the table to Autofit to Content.
I would like a way to add these into my existing code so I did not have to do it manually.
Sub Excel_to_Word()
Dim wrdApp As Object
Dim wrdDoc As Object
Dim i As Integer
Dim fName As String
Dim fLoc As String
Dim supName As String
Dim agtName As String
Dim agtCode As String
Dim rint As String
Dim evDate As String
supName = Range("M3")
MsgBox ("Supervisor Name is " & supName)
agtName = Range("C3")
rint = Range("M4")
agtCode = Range("M2")
evDate = Format(Range("M5"), "mm-dd-yy")
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add("C:\test.docx")
With ActiveSheet
.Range("A1:K41").Copy
End With
fName = agtCode & " QA " & rint & " " & evDate
MsgBox ("file name is " & fName)
fLoc = "F:\Agent Folder\" & supName & "\" & agtName & "\"
MsgBox ("Path name is " & fLoc)
wrdApp.Documents.Add.Content.PasteExcelTable False, False, True
Application.CutCopyMode = False
wrdApp.ActiveDocument.SaveAs Filename:=fLoc & fName
wrdApp.Application.Quit
End Sub
Any help you can provide would be great.
Bookmarks