Check for File Already Opened - Can't Get to Work

[Paige]

Have sub below that is used to get data from closed workbook (i.e., the
source file); it actually opens the file in read-only then closes again; it
works great. However, I can’t seem to integrate into it code to check to see
if the file which the user selects in the file dialog is already open or not.
Have tried functions, etc., but I think my problem could be that it is
opening the selected file prior to checking if it is open….just a guess
anyway. Is it possible to have Excel check to see if the file selected by
the user is open already, and if it is, then somehow allow the user to select
the source file in the Excel window, at which time Excel will pull in the
data that way? If it is not open, it would proceed as normal with the code
below. If not, then how can I modify it to just check if the file is open?

Dim WB As Workbook
Set WB = Workbooks.Open(Application.GetOpenFilename(FileFilter:="All
Files(*.*),*.xls,All Files (*.*),*.*"), True, True)
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Input and P&L")
.Range("E9").Value = WB.Worksheets("Input and P&L").Range("e9").Value
.Range("E10").Value = WB.Worksheets("Input and
P&L").Range("e10").Value
End With
WB.Close False
Set WB = Nothing
Application.ScreenUpdating = True
End Sub

 

[Jim Thomlinson]

how about something like this perhaps (untested)

Dim WB As Workbook
Dim strFileName As String

strFileName = Application.GetOpenFilename(FileFilter:="All
Files(*.*),*.xls,All Files (*.*),*.*")

On Error Resume Next
Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1, 256))
On Error GoTo 0

If WB Is Nothing Then Set WB = Workbooks.Open(strFileName, True, True)

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Input and P&L")
.Range("E9").Value = WB.Worksheets("Input and P&L").Range("e9").Value
.Range("E10").Value = WB.Worksheets("Input and
P&L").Range("e10").Value
End With
WB.Close False
Set WB = Nothing
Application.ScreenUpdating = True
End Sub

 

[Paige]

Thanks, Jim - works great! Couple of questions:
1) Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1,
256))...am not sure I understand what you're doing here...extracting the name
only (versus the entire path) of the workbook?
2) If the file is already open, can I just extract the data and leave the
file open? I don't want to automatically close the file because they may
need to save it first or keep it open. Tried to modify it as follows, but
get a subscript out of range:

Dim WB As Workbook
Dim strFileName As String

strFileName = Application.GetOpenFilename(FileFilter:="All
Files(*.*),*.xls,All Files (*.*),*.*")

On Error Resume Next
Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1, 256))
On Error GoTo 0

If WB Is Nothing Then
Set WB = Workbooks.Open(strFileName, True, True)
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Input and P&L")
.Range("E9").Value = WB.Worksheets("Input and
P&L").Range("e9").Value
.Range("E10").Value = WB.Worksheets("Input and
P&L").Range("e10").Value
End With
WB.Close False
Set WB = Nothing
Application.ScreenUpdating = True
Else
Set WB = Workbooks(strFileName)
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Input and P&L")
.Range("E9").Value = WB.Worksheets("Input and
P&L").Range("e9").Value
.Range("E10").Value = WB.Worksheets("Input and
P&L").Range("e10").Value
End With
Set WB = Nothing
Application.ScreenUpdating = True
End If

 

[Paige]

Yikes, think I answered my own question #2. If I change
Else
Set WB = Workbooks(strFileName)
to:
Else
Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1,
256))

it pulls from the open file and leaves it open.