PDA

View Full Version : Exporting excel worksheet to word keeping exact formatting



samuele
09-06-2017, 01:35 AM
Hello, I am struggling with exporting multiple excel sheets into word with VBA, while keeping the exact original source format as in the excel sheets. Although the word document is being created, the copied text is completely misaligned. In the excel worksheet, I have a template with a header and footer that I would also like copied into the word document along with the rest of the sheet. If I use UsedRange function, somehow I still cannot copy the header and footer from the excel sheet.

snb
09-06-2017, 03:33 AM
Please use code tags !!


Sub CreateWord()
Dim WdObj As Object, fname As String
fname = "NewDoc"
Set WdObj = CreateObject("Word.Application")
WdObj.Visible = False
Worksheets("OUTPUT").UsedRange.Select
Selection.Copy 'Your Copy Range
WdObj.Documents.Add
WdObj.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=0, DisplayAsIcon:=False
Application.CutCopyMode = False
If fname "" Then 'make sure fname is not blank
With WdObj
.ChangeFileOpenDirectory "c:\temp" 'save Dir
.ActiveDocument.SaveAs Filename:=fname & ".doc"
End With
Else
MsgBox ("File not saved")
End If
With WdObj .ActiveDocument.Close
.Quit
End With
Set WdObj = Nothing
End Sub

Where did you steal this horrible code ?

Please post a sample file.