Hey guys,
I have this macro that works well on Office 2010, even if the sheet "Data" is hidden.
However on Office 2013 it doesn't work, unless I unhide the sheet "Data". (it exports an empty word file)
What did I do wrong?
I have adjusted the macro in late binding just to make sure there are no issues with different versions of excel.
Sub ExcelRangeToWord()
Dim tbl As Excel.Range
Dim oWord As Object
Dim oDoc As Object
Dim oTable As Object
Dim Lastrow As Integer
'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False
'Copy Range from Excel
With ThisWorkbook.Sheets("Data")
Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
Set tbl = .Range("A107:D" & Lastrow)
End With
'Create an Instance of MS Word
On Error Resume Next
'Is MS Word already opened?
Set oWord = CreateObject("Word.Application")
'Clear the error between errors
Err.Clear
'If MS Word is not already open then open MS Word
If oWord Is Nothing Then Set oWord = CreateObject(class:="Word.Application")
'Handle if the Word Application is not found
If Err.Number = 429 Then
MsgBox "Microsoft Word could not be found, aborting."
GoTo EndRoutine
End If
On Error GoTo 0
'Make MS Word Visible and Active
oWord.Visible = True
oWord.Activate
'Create a New Document
Set oDoc = oWord.Documents.Add
'Format Doc
oDoc.PageSetup.Orientation = 1
'Copy Excel Table Range
tbl.Copy
'Paste Table into MS Word
oDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False
'Autofit Table so it fits inside Word Document
Set oTable = oDoc.Tables(1)
oTable.AutoFitBehavior 2 'wdAutoFitWindow
EndRoutine:
'Optimize Code
Application.ScreenUpdating = True
Application.EnableEvents = True
'Clear The Clipboard
Application.CutCopyMode = False
End Sub
Thank you in advance!
Bookmarks