Why it works but the macro is error in VB?

[tinfanide]

The macro runs well but when I press F5 in VB, it always returns "runtime error: 462" when it comes to the saveAs method.

Private Sub copytoWord_Click()
     
    Dim AppWord         As Word.Application
     
    Set AppWord = CreateObject("Word.Application")
    AppWord.Visible = True
    
    Sheets("Sheet1").Range("A1:B4").Copy
    AppWord.Documents.Add
  
    AppWord.Selection.Paste
     
    Application.CutCopyMode = False
     
     
   With ActiveDocument
   .SaveAs2 "C:\Users\Tin\Desktop\" & "Book1", wdFormatDocumentDefault
   End With
    
    Set AppWord = Nothing
    
     
End Sub

[macropod]

Hi Tinfanide,

The error is probably because you're not telling the calling App that it's AppWord's ActiveDocument you want to save. Try something along the lines of:

Private Sub copytoWord_Click()
Sheets("Sheet1").Range("A1:B4").Copy
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application")
With AppWord
  .Visible = True
  .Documents.Add
  .Selection.Paste
  .CutCopyMode = False
  .ActiveDocument.SaveAs2 "C:\Users\Tin\Desktop\" & "Book1", wdFormatDocumentDefault
  .Quit
End With
Set AppWord = Nothing
End Sub

[tinfanide]

Originally Posted by macropod

Hi Tinfanide,

Dim AppWord As Word.Application

Excel reports:
User-defined type not defined

[tinfanide]

Originally Posted by tinfanide

Excel reports:
User-defined type not defined

I forgot to add MS Word Object Library.

[tinfanide]

Private Sub CopyToWord()

Dim arr As Variant
arr = Array("A", "B", "C")

With ActiveSheet
.Cells.Clear

For x = 1 To 3
.Range(arr(x - 1) & "1").Value = x
Next x

.Range("A1:C1").Copy

End With

Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application")

With AppWord

.Visible = True
.Documents.Add
.Selection.Paste

End With

'''''''''''''''''''''''''''
With AppWord.ActiveDocument.Tables(0)
.Delete
End With
'''''''''''''''''''''''''''

Set AppWord = Nothing

End Sub

Same problem with the highlighted bit
Reported "Application defined or object defined error"

I remember I've learnt to set variable for the table
But here I don't know how to select the table in Word from Excel VBA

And
I want to select the table
as the .Paste command auto copy the Excel data to Word in a table format
I want to do something to the table
like deleting it, adding borders or some styles to it

So
I want to ask how I can control the word table from Excel VBA

Many thanks.

[macropod]

Hi Tinfanide,

The line:
Dim AppWord As Word.Application
is only relevant if you've already set a reference to Word.

Try:

Private Sub CopyToWord()
Dim arr As Variant, x As Long, AppWord As Object
Set AppWord = CreateObject("Word.Application")
arr = Array("A", "B", "C")
With ActiveSheet
  .Cells.Clear
  For x = 1 To 3
    .Range(arr(x - 1) & "1").Value = x
  Next x
  .Range("A1:C1").Copy
End With
With AppWord
  .Visible = True
  .Documents.Add
  With .ActiveDocument
    .Range.Paste
    .Tables(1).Delete
    '.Close SaveChanges:=False
  End With
  '.Quit
End With
Set AppWord = Nothing
End Sub

The two commented-out lines show how to correctly shut down Word without saving. Simply setting AppWord = Nothing doesn't do that - it leaves Word running and, if you re-run the code, another Word instance will be created.