Thanks for both of the replys. I'm afraid I wasn't as specific as I should
have been. I have the reference to WORD as suggested by Ko Zaw or similar
My issue is that when it's called up, the excel program is still the active
program and the excel worksheet is still the one on top. I would like to
have the WORD document, when called to take the focus and be the displayed
document to work on, with the excel document in the background. Here is my
code I'm using and this will open the WORD document and keep it in the
background. Is there something that I can add to make it come forward and be
the active display in the window???
Sub OpenWordDocument(WhichLetter As String)
'In order to use this code you must set a reference to the
'Word object library by doing this. In the VB Editor click
'Tools, References. Then search for Microsoft Word n.n Object Library
'where n.n will depend on your version of Word.
Dim objWord As Word.Application
Dim objDoc As Word.Document
Application.ScreenUpdating = True
Set objWord = CreateObject("Word.Application")
If objWord Is Nothing Then
MsgBox "Could not create the Word object"
Set objDoc = objWord.Documents.Open("C:\LettersForms\LETTERS\" & WhichLetter
& ".doc") 'actual name went here
If objDoc Is Nothing Then
MsgBox "Could not open the specified document"
Set objWord = Nothing
If WhichLetter = "03CInterview" Or WhichLetter = "03FInterview" Or
WhichLetter = "10FAgreement" Or _
WhichLetter = "11FCMP" Or WhichLetter = "12AExhibit" Or WhichLetter =
"12BExhibit" Or _
WhichLetter = "13IA" Then Exit Sub
'.OpenDataSource Name:=C:\LettersForms\Full Database.xls, _
'sqlstatement1:="SELECT * FROM [" Full Database.DataBase & "$]"
.OpenDataSource Name:="C:\LettersForms\Full Database.xls", _
sqlstatement1:="SELECT * FROM [DataBase$]"
Hello Bob R,
So very sorry, I lost my last post during sending.
Let focus the point again. I'll post the code, test at your end.
Worked fine for me.
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long
Private Declare Function ShowWindow Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Const SW_SHOWNORMAL = 1
Const gcClassnameMSWord = "OpusApp"
Public Function fActivateWindowClass(psClassname As String) As Boolean
Dim hwnd As Long
hwnd = FindWindow(psClassname, vbNullString)
If hwnd > 0 Then
ShowWindow hwnd, SW_SHOWNORMAL
ShowWindow hwnd, SW_SHOWNORMAL
fActivateWindowClass = True
End If
End Function
Sub kzCallWordbyShell(WhichLetter As String)
Dim MyWordFileFullName As String
MyWordFileFullName$ = _
"C:\LettersForms\LETTERS\" & _
WhichLetter & ".doc" 'actual name went here
Dim MyWord As Object
Dim WordWasNotRunning As Boolean
Dim UserResponse As Integer
On Error Resume Next
Set MyWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then WordWasNotRunning = True
Err.Clear
If WordWasNotRunning = False Then
UserResponse = MsgBox("Word Application is running," & vbCrLf
& _
"If you continue, " & MyWordFileFullName & " may not come-up
front, " & _
"but will be focused on taskbar" & _
vbCrLf & "<YES> Continue, <NO> Close Word, " & _
"<Cancel> halt Code", vbInformation + vbYesNoCancel)
If UserResponse = vbCancel Then
Set MyWord = Nothing
MsgBox "Hit me again! If you want to do."
Exit Sub
ElseIf UserResponse = vbNo Then
MsgBox "Word Application is closing. " & _
"Save your file as necessary and Hit me again!"
fActivateWindowClass ("OpusApp")
MyWord.Application.Quit
Set MyWord = Nothing
Exit Sub
Else
fActivateWindowClass ("OpusApp")
Set MyWord = Nothing
End If
End If
Call Shell("winword.exe """ & _
MyWordFileFullName$ & """", 3) ' 1= Normal, 3=Maximize
' Here Do your mailmerge things...
' If your document is Mailmerge Main Document,
' then it may already have datasource and try to connect
' on start-up. No need to opendatasource again I think.
End Sub
Ko Zaw
