transferspreadsheet and open files

[scotttom]

I need to see if a .xls file is open before I transferspreadsheet to it. If it is send up a msgbox telling the user to close it.

Then after the transfer I want to open the .xls file.

Easy right?

It does not seem so.

Any help out there? Thanks in advance as usual.

Scott

 

[Remou]

You can use api.

Option Compare Database

Private Const GW_HWNDNEXT = 2
Private Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Function FindWindowPartial(ByVal Title As String) As String
    Dim hWndThis As Long

    hWndThis = FindWindow(vbNullString, vbNullString)
    While hWndThis
        Dim sTitle As String, sClass As String
        sTitle = Space$(255)
        sTitle = Left$(sTitle, GetWindowText(hWndThis, sTitle, Len(sTitle)))
        If InStr(sTitle, Title) > 0 Then
            FindWindowPartial = sTitle & "|" & FindWindowPartial
        End If
        hWndThis = GetWindow(hWndThis, GW_HWNDNEXT)
    Wend
End Function

To use this:

FindWindowPartial "SomeExcelFileName.xls"

 

[JerryKlmns]

If you want to find if a file is in use not only by the user trying to check this but from any1, when you try to move it with the NAME statement, you 'll get an error.

Scroll down this thread222-1417879: Unable to close excel instance to see a similar solution