check if file with generic name already open
 

I will be grateful for any advices re the following. I have read similar but
the file names are hard coded. I have a list of files to open using a macro
and to work on so file names are held in a variable as opposed to being hard
coded into the macro.

How do i check to see if a file is already open, the name is say held in
cell A10 on sheet 1 for example and the next to open will be say A11 and so
on.
--
with kind regards

Spike

check if file with generic name already open
 

For i = 10 To Cells(Rows.Count).End(xlUp).Row
If IsFileOpen(Cells(i,"A").Value) Then
Msgbox Cells(i,"A").Value
End If
Next i


Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Spike" wrote in message
...
I will be grateful for any advices re the following. I have read similar
but
the file names are hard coded. I have a list of files to open using a
macro
and to work on so file names are held in a variable as opposed to being
hard
coded into the macro.

How do i check to see if a file is already open, the name is say held in
cell A10 on sheet 1 for example and the next to open will be say A11 and
so
on.
--
with kind regards

Spike

check if file with generic name already open
 

sub test()
Dim wb As Workbook
Dim CheckFor As String

CheckFor = Range("A10").Value

For Each wb In Workbooks
If wb.Name = CheckFor Then MsgBox (CheckFor & " is open!")
Next wb
end sub


"Spike" wrote:

I will be grateful for any advices re the following. I have read similar but
the file names are hard coded. I have a list of files to open using a macro
and to work on so file names are held in a variable as opposed to being hard
coded into the macro.

How do i check to see if a file is already open, the name is say held in
cell A10 on sheet 1 for example and the next to open will be say A11 and so
on.
--
with kind regards

Spike