Selecting MS Word 2003 Table from Excel 2003

[longbow007]

Hello, could some one please help me modify my code so that I can select a MS Word 2003 Table from my Excel 2003 macro? I keep getting Error 438 as per attached file for the line of code:

Selection.Tables(1).Select

Any help is greatly appreciated.

Kind regards,

Chris

Sub Data_to_Text()
'
' Data to Text Macro
' Macro recorded 9/02/2010
'

'
    Dim myRange As Range
    Dim LastRow As Long
    Dim docWord As Object
    
    

    Application.ScreenUpdating = False
    
    Set myRange = Selection
    
    Sheets("Data").Select
    
    For Each cell In myRange
        Range(cell.Address).Value = cell.Value
    Next cell
    
    Selection.Copy
    
    Sheets("Data to Text").Select
    
    ActiveSheet.Paste
    
    Columns("A:A").Select
    
    Application.CutCopyMode = False
    
    Selection.Insert Shift:=xlToRight
    
    Rows("1:10").Select
    Selection.RowHeight = 22.5
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row

    Range("A2").AutoFill Destination:=Range("A2:A" & LastRow)
    
    Range("G:G,I:I,J:J,K:K,M:M,N:N,O:O,P:P,Q:Q,S:S").Select
    Range("S1").Activate
    
    Selection.Delete Shift:=xlToLeft
    
    ActiveSheet.Range("A1:J1", ActiveSheet.Range("A65536").End(xlUp)).Select
    
    Selection.Copy

    Set docWord = CreateObject("Word.Application")
    docWord.Visible = True
    
    docWord.Documents.Add
    docWord.Selection.Paste
     
    Application.CutCopyMode = False
     
    Set docWord = Nothing
    
    Selection.Tables(1).Select
      
    Selection.Rows.ConvertToText Separator:=wdSeparateByDefaultListSeparator, _
        NestedTables:=True
        
    Application.ScreenUpdating = True
        
End Sub

[longbow007]

Hi again, I have just uploaded my Excel 2003 workbook: Home.xls to help with a solution to the problem described in the above post. The workbook contains "dummy-data" only.

I hope this helps.

Kind regards,

Chris

[longbow007]

Hi again, I searched the Internet and found some old VBA answers and questions at the following website:

The following is the new code that seems to work OK

I modified the following two lines of code as follows, by adding the object docWord:

docWord.Selection.Tables(1).Select
      
    docWord.Selection.Rows.ConvertToText Separator:=wdSeparateByDefaultListSeparator, _
        NestedTables:=True

Sub Data_to_Text()
'
' Data to Text Macro
' Macro recorded 9/02/2010
'

'
    Dim myRange As Range
    Dim LastRow As Long
    Dim docWord As Object
    
    

    Application.ScreenUpdating = False
    
    Set myRange = Selection
    
    Sheets("Data").Select
    
    For Each cell In myRange
        Range(cell.Address).Value = cell.Value
    Next cell
    
    Selection.Copy
    
    Sheets("Data to Text").Select
    
    ActiveSheet.Paste
    
    Columns("A:A").Select
    
    Application.CutCopyMode = False
    
    Selection.Insert Shift:=xlToRight
    
    Rows("1:10").Select
    Selection.RowHeight = 22.5
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row

    Range("A2").AutoFill Destination:=Range("A2:A" & LastRow)
    
    Range("G:G,I:I,J:J,K:K,M:M,N:N,O:O,P:P,Q:Q,S:S").Select
    Range("S1").Activate
    
    Selection.Delete Shift:=xlToLeft
    
    ActiveSheet.Range("A1:J1", ActiveSheet.Range("A65536").End(xlUp)).Select
    
    Selection.Copy

    Set docWord = CreateObject("Word.Application")
    docWord.Visible = True
    
    docWord.Documents.Add
    docWord.Selection.Paste
     
    Application.CutCopyMode = False
    
    docWord.Selection.Tables(1).Select
      
    docWord.Selection.Rows.ConvertToText Separator:=wdSeparateByDefaultListSeparator, _
        NestedTables:=True
        
    Set docWord = Nothing
        
    Application.ScreenUpdating = True
        
End Sub