Open file with a variable name

Hi all

Newbie posting, please be gentle

I am hoping that you excel wizards will be able to help me out. I got the basis of the code below online in a free excel help webpage.

The code checks to see if a particular excel file is open and if it is then calls another macro (to copy and past some of the contents of the target file) into action.

However what I want to do is to make the file name which is checked for variable - ie in the example below as long as "2830 V 6 dec 05.xls" OR "2830 V 6 dec 06.xls" (or any other year) is open then the 'CopyInfo' macro should be called.

Many thanks in advance





Sub IsWorkBookOpenDec()


'Test to see if a Workbook is open.


Dim wBook As Workbook

On Error Resume Next
Set wBook = Workbooks("2830 V 6 Dec.xls")

If wBook Is Nothing Then 'Not open
Range("f3").Select
MsgBox "'2830 V 6 Dec.xls' is not open", _
vbCritical, ""
Set wBook = Nothing
On Error GoTo 0
Else 'It is open
Call CopyInfo
On Error GoTo 0
End If
End Sub

Variables added

You will need to add the correct coding to set the sfname variable to the required book name. I have set it using the book name from your posted macro

Sub IsWorkBookOpenDec()


'Test to see if a Workbook is open.


Dim wBook As Workbook
Dim sFname As String


sFname = "2830 V 6 Dec.xls"
On Error Resume Next
Set wBook = Workbooks(sFname)

If wBook Is Nothing Then 'Not open
Range("f3").Select
MsgBox sFname & " is not open", _
vbCritical, ""
Set wBook = Nothing
On Error GoTo 0
Else 'It is open
Call CopyInfo
On Error GoTo 0
End If
End Sub