Problems when transfering data from MS Excel to MS Word.

[JensKKK]

Hi guys,

I have written a little bit code which transfers some data from MS Excel to MS Word.

Q(1) The problem is that MS Word is not always installed on the path that I have writen into my program. Is there a better way to figure out where MS Word is installed.


Q(2) The program code does not work when MS Word is running. Any ideas why that is happening.

Thanks

Program code see below.

'Select text to paste into MS Word
Sheets("Result table").Select
Cells(Top - 10, 2).Resize(Number_of_Hits + 11, 8).Select
Selection.Copy

'open MS Word an print Biomarker discovery report

Dim appword As Word.Application
On Error Resume Next
AppActivate "Microsoft Word"
'if Microsoft Word is not running, start and activate it

If Err Then
Shell "C:\Program Files\Microsoft Office\Office10\" _
& "Winword /Automation", vbMaximizedFocus
AppActivate "Microsoft Word"
End If
On Error GoTo fehler

'Get an application object so you can autoamte word
Set appword = GetObject(, "Word.application")

With appword
.Documents.Add DocumentType:=wdNewBlankDocument
End With
With appword.ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
End With
With appword
.Selection.Paste
End With

Exit Sub

'Error handler. Just in case
fehler:
msg = MsgBox("Error while opening MS Word", vbOKOnly)

 

[PHV]

Use the CreateObject function instead of the Shell ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JensKKK]

Thanks PHV,

any chance of explaining the subject a little bit more explicit.

What do I use for Class as string and Servername as string.

Thanks

 

[PHV]

A starting point:
Dim appword As Word.Application
Set appword = CreateObject("Word.application")
With appword
.Documents.Add DocumentType:=wdNewBlankDocument
.ActiveDocument.PageSetup.Orientation = wdOrientLandscape
.Selection.Paste
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886