Error Resolution

[tljenkin]

Hi All,

I have the following code below. If the user says yes when in fact the file is not open, I want excel to prompt a message "Sorry appears file is not open, try again". Otherwise continue as normal.

Please help!

Sub UpdateDatabase()

Dim FileToBeOpened As Variant, File As Variant, Ard As Workbook, Wrr As Workbook
Set Ard = ThisWorkbook ' or Actuals repository current version presently open

Application.ScreenUpdating = False

Dim Response As Integer
Response = MsgBox(prompt:="Is the most recent weekly Redress report open?", Buttons:=vbYesNo)
If Response = vbYes Then


For Each Wrr In Application.Workbooks
If Wrr.Name Like "Week*" Then Wrr.Activate
Next Wrr
Set Wrr = ActiveWorkbook

 

[tljenkin]

I guess that didnt read very clear.

What I meant to say was this:

If you expect the user to answer yes if a file is already open or no if its not, if for example they answer yes its open when in fact its not, can excel end the sub with a message that "the file is not open"? How do I code this?

Many thanks

 

[tljenkin]

Presently I get an error message runtime error 9 - subscript out of range

 

[Andrew Poulsom]

There's a function you can use here:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

 

[tljenkin]

But i am asking excel to look for a file with a wildcard, see code. Can i use the file exist code with a wild card?

 

[Andrew Poulsom]

Try:

    Dim WB As Workbook
    Response = MsgBox(prompt:="Is the most recent weekly Redress report open?", Buttons:=vbYesNo)
    If Response = vbYes Then
        For Each Wrr In Application.Workbooks
            If Wrr.Name Like "Week*" Then
                Set WB = Wrr
                WB.Activate
                Exit For
            End If
        Next Wrr
    Else
        Exit Sub
    End If
    If WB Is Nothing Then
        MsgBox "The file is not open."
        Exit Sub
    End If