Open File IF Not Opened

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Goodmorning!

I was looking for someone who could tell me how I could make a Macro
Appliction open files only when there not opened. When a file is allready
opened it should be left at alone.

When a file is being opened, this file should be granted the opportunity to
update it's pivot tables and carry out any external links.

I hope someone has the answer! Thanks in advance!
 
Here's some code that might help. It tests whether a file is open: if it is
it activates it, otherwise it opens the file.

If IsWindowOpen(lStr_FileName) Then
Windows(lStr_FileName).Activate
Else
Workbooks.Open FileName:=lStr_Path & lStr_FileName
End If


''
***************************************************************************
'' Purpose : Test if a specific file is open
'' Written : 29-Jun-2001 by Andy Wiggins
'' Syntax : IsWindowOpen("FileName.Xls")
'' Returns : "True" or "False"
''
Function IsWindowOpen(pstr_WindowName As String) As Boolean
Dim w As Window

For Each w In Windows
If w.Caption = pstr_WindowName Then
IsWindowOpen = True
Exit Function
End If
Next

IsWindowOpen = False

End Function


--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
 
Update:

I've come up with the following:

Sub GOTOFile2()
Application.DisplayAlerts = False
On Error Resume Next
Workbooks("File2.xls").Activate
On Error Resume Next
Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=1
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

Problem however is, it will go back to the saved version. This is something
I absolutely don't want to happen. Does anyone know something to get rid of
this problem? Thanks in advance!
 
On Error Resume Next
Set oWB = Workbooks("File2.xls")
On Error Goto 0
If oWB Is Nothing Then
Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=1
End If
 
Andy Wiggins wrote...
Here's some code that might help. It tests whether a file is open: if it is
it activates it, otherwise it opens the file.

If IsWindowOpen(lStr_FileName) Then
Windows(lStr_FileName).Activate
Else
Workbooks.Open FileName:=lStr_Path & lStr_FileName
End If

''
***************************************************************************
'' Purpose : Test if a specific file is open
'' Written : 29-Jun-2001 by Andy Wiggins
'' Syntax : IsWindowOpen("FileName.Xls")
'' Returns : "True" or "False"
''
Function IsWindowOpen(pstr_WindowName As String) As Boolean
Dim w As Window

For Each w In Windows
If w.Caption = pstr_WindowName Then
Click to expand...
....

Why iterate through *Windows* rather than through *Workbooks*? There
are potential problems with your approach. First, if some files have
multiple windows, then those windows' .Caption properties would include
':#' tags following the filename, so your function is guaranteed always
to return FALSE when the file in question happens to have multiple
windows unless the user is clever enough to append ':1' to the filename
argument. Also, you'd iterate through all windows for workbooks with
multiple windows, which is unnecessary and a slight performance drag.

Second, the .Caption property of windows *CAN* *BE* *CHANGED* from the
workbook's filename, so there's no guarantee your approach would work
even if the file in question were open with only a single window.

If you want to iterate, *FAR* *BETTER* to iterate over the Workbooks
collection and check the .Name property. However, better still not to
iterate at all. See Bob Phillips's response.
 
Bob Phillips wrote...
On Error Resume Next
Set oWB = Workbooks("File2.xls")
On Error Goto 0
If oWB Is Nothing Then
Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=1
End If
Click to expand...
....

Quibble: since Excel only allows one file with a given base filename to
be open at a time, your approach wouldn't open C:\File2.xls if
D:\z\File2.xls were already open. One approach to dealing with that
would be


On Error Resume Next
Set oWB = Workbooks(basefilename)
On Error Goto 0

If oWB.FullName <> filepath & basefilename Then
If MsgBox( _
Prompt:="Do you want to close this file and open" & Chr(13) & _
"""" & filepath & basefilename & """?", _
Title:="""" & oWB.FullName & """ Already Open", _
Buttons:=vbYesNo) = vbYes Then
oWB.Close
Set oWB = Nothing
End If

If oWB Is Nothing Then
Workbooks.Open Filename:=filepath & basefilename, UpdateLinks:=1
End If
 
Back
Top