Excel 2007 on XP Pro:
I am trying to write a a macro in Excel which will:
(1) open a workbook from another only if not already open
(2) close the workbook from another only if the other workbook is already
open.
Any ideas?

http://groups.google.pl/group/micros... 02811d5cf9d2

or Search for "is file open" in this NG


On 10 Lis, 12:28, Wes_A wrote:
Excel 2007 on XP Pro:
I am trying to write a a macro in Excel which will:
(1) open a workbook from another only if not already open
(2) close the workbook from another only if the other workbook is already
open.
Any ideas?

Try using this function. I'm sure I got it from here at some point.

Option Explicit
Function OpenWorkbook() As Excel.Workbook
Dim sFile As String
Dim ShortName As String

Set OpenWorkbook = Nothing

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Title = "Please Select File to open"
If .Show = False Then Exit Function
sFile = .SelectedItems(1)
End With


ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))



On Error Resume Next
Set OpenWorkbook = Workbooks(ShortName)
On Error Resume Next

If OpenWorkbook Is Nothing Then
Application.AutomationSecurity = msoAutomationSecurityLow
OpenWorkbook = Workbooks.Open(sFile)
Application.AutomationSecurity = msoAutomationSecurityByUI
End If

End Function
--
HTH,

Barb Reinhardt



"Wes_A" wrote:

Excel 2007 on XP Pro:
I am trying to write a a macro in Excel which will:
(1) open a workbook from another only if not already open
(2) close the workbook from another only if the other workbook is already
open.
Any ideas?