1

I need to convert the embedded excel sheet object to word table in word file, I am currently using is to open the embedded excel sheet object , select the content and paste to word. Could there be a better way to simplify this action?

I try to create a mirco but .OLEformat keep error and said this member cannot be accessed on a horizontal line.

Sub ConvertXLObjs()
Application.WindowState = wdWindowStateMinimize
Dim i As Long, j As Long, k As Long, Rng As Range, bDel As Boolean
Dim objOLE As Word.OLEFormat, objXL As Object
With ActiveDocument
For i = .InlineShapes.Count To 1 Step -1
With .InlineShapes(i)
  If Not .OLEFormat Is Nothing Then
    If Split(.OLEFormat.ClassType, ".")(0) = "Excel" Then
      Set Rng = .Range
      Set objOLE = .OLEFormat
      objOLE.Activate
      Set objXL = objOLE.Object
      With objXL.ActiveSheet
        .Range("$A$1:" & _
        .Cells.SpecialCells(11).Address).Copy ' 11 = xlCellTypeLastCell
      End With
      objXL.Application.Undo
      .Delete
      With Rng
        .Characters.First.PasteAndFormat wdTableInsertAsRows
        .MoveEnd wdParagraph, 2
        With .Tables(1)
          .AllowAutoFit = False
          .BottomPadding = 0
          .LeftPadding = 0
          .RightPadding = 0
          .TopPadding = 0
          .Rows.AllowBreakAcrossPages = False
          .Rows.HeightRule = wdRowHeightExactly
          If .Uniform = True Then
            For j = .Columns.Count To 1 Step -1
              bDel = True
              For k = 1 To .Columns(j).Cells.Count
                If Len(.Columns(j).Cells(k).Range.Text) > 2 Then
                  bDel = False
                  Exit For
                End If
              Next
              If bDel = True Then
                .Columns(j).Delete
              Else
                Exit For
              End If
            Next
          End If
        End With
      End With
    End If
  End If
End With
Next
End With
Set objXL = Nothing: Set objXL = Nothing: Set Rng = Nothing
Application.WindowState = wdWindowStateNormal
MsgBox "Finished processing!"
End Sub

the code is refer from https://social.msdn.microsoft.com/Forums/office/en-US/5955da06-725d-45f2-aa1b-5eb37c0646c6/how-to-convert-all-embeded-excel-sheets-in-word-into-words-tables?forum=worddev

Example: Example

Chau Chun
  • 95
  • 1
  • 6

0 Answers0