Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 11-25-2011, 08:29 AM
tinfanide tinfanide is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2011
Posts: 308
tinfanide is on a distinguished road
Default Why it works but the macro is error in VB?

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


Code:
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

Reply With Quote
  #2  
Old 11-30-2011, 04:28 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 17,649
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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:
Code:
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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 12-02-2011, 05:16 AM
tinfanide tinfanide is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2011
Posts: 308
tinfanide is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Hi Tinfanide,
Code:
Dim AppWord As Word.Application
Excel reports:
User-defined type not defined
Reply With Quote
  #4  
Old 12-02-2011, 07:39 PM
tinfanide tinfanide is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2011
Posts: 308
tinfanide is on a distinguished road
Default

Quote:
Originally Posted by tinfanide View Post
Excel reports:
User-defined type not defined
I forgot to add MS Word Object Library.
Reply With Quote
  #5  
Old 12-02-2011, 08:00 PM
tinfanide tinfanide is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2011
Posts: 308
tinfanide is on a distinguished road
Default

Code:
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.
Reply With Quote
  #6  
Old 12-03-2011, 12:53 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 17,649
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Tinfanide,

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

Try:
Code:
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Simple VBA macro error Formulayeti PowerPoint 1 12-09-2011 10:02 PM
Runtime Error 4120 in Word 2007 macro Frankwlc Word 5 11-28-2011 01:54 AM
Works to word Janette Word 1 11-12-2011 08:23 AM
Macro Error 5174 muster36 Word 0 08-12-2011 03:34 AM
Converting from Works Menno Hershberger Office 0 09-01-2010 11:33 PM


All times are GMT -7. The time now is 01:36 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft