Copy Word into Excel and clear Clipboard message

[WaltzAir]

Could not get the Word App to quit without the "Large amount of data in clipboard" dialog and Word has no way to avoid it, so sendkeys seemed the easy way out ...
Anyone know a more elegant method?

Sub Word_to_Excel()
'
Dim wApp As Word.Application, gdoc As String
'
Set wApp = CreateObject("Word.Application")
'
gdoc = Application.GetOpenFilename("Word Files (*.doc*)," & "*doc*")
wApp.Visible = True
wApp.Documents.Open (gdoc)
wApp.Selection.WholeStory
wApp.Selection.Copy
Range("A1").Activate
wApp.Documents(1).Close
wApp.Quit
AppActivate wApp
Application.SendKeys ("{ENTER}")
Set wApp = Nothing
'
End Sub

 

[schielrn]

You can use:

wapp.cutcopymode = false

But that is going to clear what ever is on the clipboard, so you would have to do your paste into excel before you closed the word app.

You might be able to put this before the close:

wapp.displayalerts = false

and then after the close put:

wapp.displayalerts = true

Hope one of those methods will work for you?

 

[patel45]

Sub Word_to_Excel()
Set wApp = CreateObject("Word.Application")
gdoc = Application.GetOpenFilename("Word Files (*.doc*)," & "*doc*")
wApp.Visible = True
wApp.Documents.Open (gdoc)
wApp.Selection.WholeStory
wApp.Selection.Copy
Range("A1").PasteSpecial
wApp.Documents(1).Close
wApp.Quit
Set wApp = Nothing
End Sub

 

[NdNoviceHlp]

Set the document contents to a range and then convert the range to a string something like...

Dim LastParaLoc As Integer, Mydata$, MyRange As Variant
Wapp.ActiveDocument.Select
LastParaLoc = Wapp.Selection.paragraphs.Count 'last paragraph(line#)
Set MyRange = Wapp.ActiveDocument.paragraphs(1).Range
    MyRange.SetRange Start:=MyRange.Start, _
        End:=Wapp.ActiveDocument.paragraphs(LastParaLoc).Range.End
MyRange.Select
Mydata = Wapp.Selection.Text

Not sure if all this is necessary. Maybe just...

wApp.WholeStory.Select
Mydata = Wapp.Selection.Text

In either way, convert the contents of the doc to a string variable will eliminate the clipboard problem. HTH. Dave

 

[WaltzAir]

If only the Word application recognized those methods ...

 

[schielrn]

Are you referring to my post? If so, it does recognize these:

Application.DisplayAlerts = wdAlertsNone
Application.DisplayAlerts =wdAlertsMessageBox

Hope that helps.

 

[WaltzAir]

I had tried the wApp.DisplayAlerts = False to no avail (Word vba did not recognize).
Since I code Excel primarily, I did not know that syntax.

Are you referring to my post? If so, it does recognize these:

Application.DisplayAlerts = wdAlertsNone
Application.DisplayAlerts =wdAlertsMessageBox

Hope that helps.

 

[schielrn]

With this being run from Excel you may have to do something like:

wapp.displayalerts = 0 'wdalertsnone
wapp.displayalerts = -2 'wdalertsmessagebox

Since excel will probably not recognize the word built-in constants.

 

[WaltzAir]

Must be some other problem. The Editor autocompletes the code line, including the enumerations. I tried both the hard number and the constant, but neither work -- the alert still pops up.
The sendkeys still work, though.

With this being run from Excel you may have to do something like:

wapp.displayalerts = 0 'wdalertsnone
wapp.displayalerts = -2 'wdalertsmessagebox

Since excel will probably not recognize the word built-in constants.