How to re-display Excel after opening and closing a Word document

[El_Diabolo]

Hi All,

I am using Excel 2010, 32 bit.

I use a button on an Excel worksheet to run a macro which minimises Excel (Application.WindowState = xlMinimized) and then opens MS Word and displays a document. That all works just fine. What I can't figure out is how to re-display the Excel worksheet after closing the Word document. Of course I can simply click on the Excel icon on the taskbar, but I would prefer if the process could be automated so that users are returned to the Excel worksheet whence they came. Any help would be much appreciated.

Best regards.

[sassora]

Maybe try something like this?
http://www.cpearson.com/excel/ActivateExcelMain.aspx

[El_Diabolo]

sassora, thanks for your response. Unfortunately, I don't think it will work, but I may be misunderstanding what the link describes. What I am doing is: opening an Excel document from a desktop icon, pressing a button on the Excel sheet, minimising the Excel window, starting, or attaching to(if already open), Word, opening and displaying a document and then (whether amended or not) closing the Word document. At this point I am now looking at the desktop with Excel on the task bar. At no point am I involved in code within Word which, if I understand the link, is where the API call goes. Sorry if I am being thick.

Best regards.

[sassora]

I'm sure this isn't the best way, could you do something like?:

(assuming you know the path of the word doc already)

[vba]Sub OpenFile()
Dim sFile As String
Dim IsFileLocked As Boolean
sFile = "C:\Test.doc"IsFileLocked = False

On Error Resume Next

Do Until IsFileLocked = False
' \\ Open file, if error occurs the document is open
Open sFile For Binary Access Read Write Lock Read Write As #1
Close #1

If Err.Number <> 0 Then IsFileLocked = True
Err.Clear
End If
Loop

Application.WindowState = xlMaximized
End Sub[/vba]

[El_Diabolo]

Thank you again, sassora. I have tried to incorporate your code into mine, but I am getting an "Expected End of Statement" error. I am not familiar with the type of "Open" you specify and don't know the syntax of the statements involved. Therefore, I can't correct it. Also, I don't understand what the "Open" statement is doing. Sorry. The code I am using is below, DocName instead of sFile. Apologies for taking up so much of your time.

Sub Agenda_Open()
    Application.WindowState = xlMinimized
    Dim DocName As String
    DocName = "C:\Meeting 2013\Agenda 2013.docx"
    Call GetWord(DocName)
End Sub


Sub GetWord(ByVal DocName As String)
    Dim oWord As Object
    On Error Resume Next
    Set oWord = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
       Set oWord = CreateObject("Word.Application")
    End If
    
    Dim IsFileLocked As Boolean
    IsFileLocked = False
    On Error Resume Next
    Do Until IsFileLocked = False
         ' \\ Open file, if error occurs the document is open
        oWord.documents.Open DocName For Binary Access Read Write Lock Read Write As #1                    ' Error on this line
        oWord.Visible = True
        Close #1
         
        If Err.Number <> 0 Then IsFileLocked = True
        Err.Clear
        End If
    Loop
 
Application.WindowState = xlMaximized
     
End Sub

Best regards.

[snb]

No need tot minimize Excel:

sub M_snb()
  with getobject("G:\OF\example.docx")
     .windows(1).visible=true
     ----
     .close 0
  end with
End Sub

[El_Diabolo]

Thanks, snb, for your reply. Since I'm not sure just what to do with your code, or what else I may have to remove, I have tried incorporating it in a few different ways, but without success. What happens is I get the Word icon on the task bar, but when I click it no document is displayed. I'm afraid I would need more explicit instructions regarding your code.

Best regards.

[sassora]

Using FollowHyperlink seems to be an even shorter way to get the word doc to open.

Is this closer to what you are looking for?

[VBA]Sub TestVBA()

Const strFileToOpen As String = "g:\of\example.docx"


If IsFileOpen(strFileToOpen) = True Then
MsgBox "File already open"
Exit Sub
Else


ActiveWorkbook.FollowHyperlink strFileToOpen


Application.WindowState = xlMinimized
Do Until IsFileOpen(strFileToOpen) = False
Loop
End If


Application.WindowState = xlMaximized


End Sub








Function IsFileOpen(strFullPathFileName As String) As Boolean
'// VBA version to check if File is Open
'// We can use this for ANY FILE not just Excel!
'// Ivan F Moala
'// http://www.xcelfiles.com


Dim hdlFile As Long


'// Error is generated if you try
'// opening a File for ReadWrite lock >> MUST BE OPEN!
On Error GoTo FileIsOpen:
hdlFile = FreeFile
Open strFullPathFileName For Random Access Read Write Lock Read Write As hdlFile
IsFileOpen = False
Close hdlFile
Exit Function
FileIsOpen:
'// Someone has it open!
IsFileOpen = True
Close hdlFile
End Function[/VBA]

Why are you keen on minimising the Excel window?

sassora

[El_Diabolo]

sassora - thank you so much for your time and trouble. It really is appreciated. Firstly, before trying your code, I will answer your question about why I want to minimise Excel (just in case there is a simpler solution. I may be barking up the wrong tree altogether). The reason is simply that although the code I was/am using opened the Word document, it did not display it. Rather the Word icon appeared on the task bar, the Excel workbook remaining open and on display. Again, this is not a major issue in that it doesn't corrupt data, or fail completely. It's just not as user-friendly as one would want. I hope that explains why I am minimising Excel. I would be perfectly happy to do nothing to Excel, provided the Word document became the active window and displayed as I would like. I will wait a little while before trying the code you have provided, in case you wish to respond to this explanation. Thanks again.

Best regards, El_Diabolo.

[sassora]

In that case, this may be sufficient!:

[vba] Sub TestVBA()
ActiveWorkbook.FollowHyperlink "g:\of\example.docx"
End Sub[/vba]

[El_Diabolo]

sassora - you're a GENIUS!!!! Works a treat. How simple is that? Thankfully, not only are you a VBAX expert, but you also had the good sense to ask why I wanted to do what I was trying to do. No substitute for common sense. Just can't thank you enough. Thanks for all your time and patience ( and a super-slick solution).

Best regards,
El_Diabolo