VBA Code to open a file and try again if that file isnt there

[mturner737]

Hello,

I am trying to write VBA code that will open a file with the following name:

"Price Overrides"

However, sometimes the name of this file is "Price Overrides-1", or "Price Overrides-2" depending on when I lasted open/closed my browser/excel which can be hard to keep track of sometimes. So, I am looking for a VBA code that will essentially loop until the file is opened and then end. Kind of life a large "IF THEN ELSE" sequence.

So it would be like...

Open file "Price Overrides" If found then end sequence ELSE Open file "Price Overrides-1" if "Price-Overrides-1" is found then end sequnce else open file "Price Overrides-2" and so on and so on...

Can someone please help with this?

Thanks,

Matt

 

[JLGWhiz]

The following code is not fully tested. But should work if the following assumptions are true. It was assumed that the files would be in the same directory as the workbook running the code. It was assumed that the first two words of the file name would always be "Price Overrides" as show in the quote marks. If either of these two assumtions are not true, then the code fails. To run the code, copy it to your standard code module1. The code will look in the directory for files beginning with "Price Overrides" and with a .xls* extension. The asterisk wildcard was used in case some of the file are macro enabled ano some not. The code will create an array named myFile for the file names found. When the array is complete it will display a message box which shows the user a file name and asks if they want to open the file, if yes then the workbook will open, the user will then be asked if they want to exit the sub so they can work with that file. If Yes the procedure ends. If No, they will be aked if they want to close the just opened file. If Yes, the file will close and the the message box will then display the next file. If No, the file will remain open and the message box will display the next file. All of the previons steps will be repeated untill all retrieved files have been displayed.

Sub guessOpen()
Dim sh As Worksheet, lr As Long, rng As Range, myFile As Variant
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "Price Overrides*.xls*")
    Do
        n = n + 1
        ReDim Preserve myFile(1 To n)
        myFile(n) = fName
        fName = Dir
    Loop While fName <> ""
    For i = 1 To UBound(myFile)
        ans = MsgBox("File name " & myFile(i) & " was found.  Do you want to open it?", vbYesNo + vbQuestion, "FOUND FILE")
        If ans = vbYes Then
            Workbooks.Open myFile(i)
            resp = MsgBox("Do you want exit this procedure?", vbYesNo + vbQuestion, "EXIT MACRO?")
                If resp = vbYes Then
                    Exit Sub
                End If
            alt = MsgBox("Do you want to close this workbook and try a different one?", vbYesNo + vbQuestion, "CLOSE WORKBOOK?")
                If alt = vbYes Then
                    Workbooks(myFile(i)).Close False
                End If
        End If
    Next
End Sub

 

[mturner737]

Hi JLGWHIZ,

Thanks for the feedback. I'm a little busy at work now so won't be able to completely test the code you provided tonight, but i can tomorrow. However, I can briefly provide some more feedback to some of your points.

The sheet that I am looking for the code to activate is a worksheet that I am exporting from a website. Depending on the last time i reset my browser/excel depends if the name of the file is "Price Overrides", "Price Overrides-1", "Price Overrides-2", and so on. As a note there are never more than one of these files open at a time. But when exporting the file from the website I don't know in advance what the exact name will be (i.e. 1,2, 3 etc), so I want the VBA code to take into account this variable and just know to continue looking if the file isnt found. The code that I have asked for is only the first step of the code that I will be implementing. After this sheet has been activated I will be copying and pasting the data from it to the workbook where the macro is located, where more code will be implemented.

If this changes any of what you have provided can you advise how the script would need to be amended? If not I will proceed with the script tomorrow and follow up on this message board.

Thank you for your help.

Matt

 

[JLGWhiz]

Hi JLGWHIZ,

Thanks for the feedback. I'm a little busy at work now so won't be able to completely test the code you provided tonight, but i can tomorrow. However, I can briefly provide some more feedback to some of your points.

The sheet that I am looking for the code to activate is a worksheet that I am exporting from a website. Depending on the last time i reset my browser/excel depends if the name of the file is "Price Overrides", "Price Overrides-1", "Price Overrides-2", and so on. As a note there are never more than one of these files open at a time. But when exporting the file from the website I don't know in advance what the exact name will be (i.e. 1,2, 3 etc), so I want the VBA code to take into account this variable and just know to continue looking if the file isnt found. The code that I have asked for is only the first step of the code that I will be implementing. After this sheet has been activated I will be copying and pasting the data from it to the workbook where the macro is located, where more code will be implemented.

If this changes any of what you have provided can you advise how the script would need to be amended? If not I will proceed with the script tomorrow and follow up on this message board.

Thank you for your help.

Matt

Based on this explanation, you can disregard the code I provided. It does not apply. However, you might be able to just use the Workbooks.Open("Price Overrides*.xls*") to open the file. It should open it, no matter what suffix or dash number has been added.