none
how can I turn all worksheet-OLE to word tables in a word document with VBA? RRS feed

  • Question

  •  I wrote a piece of code, but it contains many mistakes. Dose anybody kown how to deal with this issue?  I need help, it's very emergency.  I am very appreciate for your help.

    Sub TurnSheetToTable()
    Dim myDoc As Object
      Set myDoc = ActiveDocument
      For Each oleShape In ActiveDocument.InlineShapes
            If InStr(1, oleShape.OLEFormat.ProgID, "Excel") And oleShape.OLEFormat.DisplayAsIcon = False Then
                Dim myTable As Table
                Dim myXls As Object
               
                Dim result As String
                Dim exApp As OLEFormat
                Dim tempRange As Range
                
                tempRange = oleShape.OLEFormat.Object.Worksheets(1).Range("B1").CurrentRegion.Copy
               
                oleShape.Select
                myDoc.Selection.PasteExcelTable False, False, True
             
                           
          End If
      Next oleShape

    End Sub




    • Edited by Alice Sun Friday, April 19, 2013 11:10 AM
    Friday, April 19, 2013 1:49 AM

Answers

  •         I have solved this, the main code as below:

             myXls.Activate
             myXls.Object.worksheets(1).UsedRange.CurrentRegion.Copy
             Selection.PasteExcelTable False, True, False

    Monday, September 16, 2013 3:29 AM

All replies

  • You don't provide sufficient information

    1. The version of Word involved

    2. What you expect the code to do

    3. What the code is doing wrong

    4. If you're getting errors, what are the exact error messages and which lines of code trigger them


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, April 19, 2013 5:47 AM
    Moderator
  • Thank you for your help. I am a newer to VBA. I do not know how to express, I will sort out my needs.

    Friday, April 19, 2013 9:31 AM
  • For Each oleShape In ActiveDocument.InlineShapes

         If InStr(1, oleShape.OLEFormat.ProgID, "Excel") And oleShape.OLEFormat.DisplayAsIcon = False Then

             oleShape.OLEFormat.ConvertTo _ClassType:="Word.Table"  'error here

        End If

     Next oleShape

    Friday, April 19, 2013 11:40 AM
  •         I have solved this, the main code as below:

             myXls.Activate
             myXls.Object.worksheets(1).UsedRange.CurrentRegion.Copy
             Selection.PasteExcelTable False, True, False

    Monday, September 16, 2013 3:29 AM