Help with pasting into Word

[McLaren]

Greetings everyone, I was hoping someone could help me out. I have some excel files that use VBA to paste information into word which worked fine with older versions of Office. Now I am on a new computer with Office 2007 and everything got hosed. The file is still being created on the desktop, but now there is nothing in it. If I try to paste into notepad a blank line shows up with nothing else, however if I try to paste back into excel the data is there.

Thank you in advance for your time, any replies are appreciated.

    Dim appWD As Word.Application
    Set appWD = CreateObject("Word.Application.8")
    appWD.Visible = False
    Sheets("Calculator").Select
        Sheets("Template").Range("A2").Value = Range("J1").Value
        Sheets("Template").Range("A39").Value = Range("J20").Value
        Sheets("Template").Select
        Range("A1:A49").Copy
appWD.Documents.Add
appWD.Selection.PasteSpecial DataType:=wdPasteEnhancedMetafile
appWD.ActiveDocument.SaveAs FileName:="C:\Documents and Settings\All Users\Desktop\cans.nc", FileFormat:=wdFormatText
appWD.ActiveDocument.Close
appWD.Quit
Sheets("Calculator").Select

 

[24HG_Hoop]

I would assume that the only problem it has is with the object declaration ("Word.Application.8")

I think 2007 is .12 instead of .8


I'm no whiz at this, and I don't have 2007 yet, but that seems like the most obvious thing.

 

[McLaren]

I tried that, but it didn't change anything. Thanks for the help anyways.
Any other ideas?

 

[24HG_Hoop]

Does debugger help at all? If it freezes, what line does it highlight?

It may take selecting each action, hitting F1 and seeing if that action is still part of 2007.



This is exactly why I don't want to convert to 2007...

 

[McLaren]

No help from debugger. It walked through the code without any problem. Same result; when I open up the text file it has one blank line, and that's it.

 

[ZVI]

Use this:
Dim appWD As Object
Set appWD = CreateObject("Word.Application")

And replace wdPasteEnhancedMetafile to value 9 and wdFormatText to 2 because of late binding using

 

[McLaren]

Still no change ZVI. I can manually paste back into excel exactly what I want, but when I try to manually paste into notepad/word nothing shows up.

 

[ZVI]

Here is working modification of your code:

<font face=Courier New>
Sub WhyUseWordForThat()
  Dim appWD As Object
  Set appWD = CreateObject("Word.Application")
  appWD.Visible = False
  With Sheets("Template")
    .Range("A2").Value = .Range("J1").Value
    .Range("A39").Value = .Range("J20").Value
    .Range("A1:A49").Copy
  End With
  appWD.Documents.Add
  appWD.Selection.Paste
  appWD.ActiveDocument.SaveAs Filename:="C:\cans.nc", FileFormat:=2
  appWD.ActiveDocument.Close
  appWD.Quit
  Sheets("Calculator").Select
  Application.CutCopyMode = False
End Sub
</FONT>

By why you use Work application for exporting of Excel cells values?
I mean that using of VBA Excel is simpler for that purpose.

Vladimir

 

[ZVI]

The same export from range to text file but without Word Application (it is much faster):

<font face=Courier New>
Sub Range2Txt()
  Dim FileName$, s$, FileNo%, arr, x
  FileName = ThisWorkbook.Path & "\cans.nc" ' <== use your pathname
  With Sheets("Template")
    .Range("A2").Value = .Range("J1").Value
    .Range("A39").Value = .Range("J20").Value
    arr = .Range("A1:A49")
  End With
  For Each x In arr
    s = s & x & vbCrLf
  Next
  On Error Resume Next
  Kill FileName
  FileNo = FreeFile
  Open FileName For Binary Access Write As #FileNo
  Put #FileNo, , s
  Close #FileNo
End Sub</FONT>

Regards,
Vladimir

 

[McLaren]

Thanks, I'll try it in the morning when I get back to work.