Open Workbook with Partial Name

[Littlemalky]

Is there a way to open a workbook with part of the name?

For example, I have a folder path that is static, but the file name will change every quarter. Currently, it's "BO 1st Q 2011.xlsx", is there a way to represent this, or just open it from the BO part being that it is the only file in that folder with BO in the title.

 

[Gary McMaster]

If you're willing to switch to a macro enabled workbook (.xlsm) you could do it with some code.

In its simplest form:

Public Sub Test1()

Dim sFile As String

sFile = Dir("X:\Temp\BO*.xlsx") 'Change path to suit

If sFile <> "" Then
    MsgBox sFile
    'Open file here
Else
    MsgBox "File not found"
End If

End Sub

Or if you wanted to read through all the files in the directory:

Public Sub Test2()

Dim sStartPath As String
Dim sFileName As String
Dim sFileExt As String
Dim sBOFile As String 'File to open

sFileExt = "*.*" 'Change to suit
sStartPath = "X:\Temp\" 'Change to suit

'Read the first file in the directory
sFileName = Dir(sStartPath + sFileExt, vbNormal)

'If the two left characters are "BO" assume as targeted file
If Left(UCase(sFileName), 2) = "BO" Then
    sBOFile = sFileName
End If

Debug.Print sFileName

'Read thru rest of files in directory
Do While sFileName <> ""

    sFileName = Dir
    
    'If the two left characters are "BO" assume as targeted file
    If Left(UCase(sFileName), 2) = "BO" Then
        sBOFile = sFileName
    End If
    Debug.Print sFileName
    
Loop

If sBOFile <> "" Then
    MsgBox "Open: " & sBOFile
    'Open file here
Else
    MsgBox "No file found"
End If

End Sub

Hope this helps.

Gary

 

[Littlemalky]

I used the second piece of code because I can't change the workbook. However, when I run it, all it does it pop up the Msgbox line "Open" with my exact file name. Then I click ok, but nothing happens after that, it doesn't open. So atleast it's finding the file.

 

[Ruddles]

There's no code in there to open the file - the MsgBox is merely there to indicate to you what the code is doing. You have to replace 'Open file here with your own code to open the file and do whatever you need to do with it. (Don't forget to close it again afterwards!)

 

[Littlemalky]

Yeah, just realized that ha. I'm a noob at this, how do i refer to the file now to open it?

 

[Gary McMaster]

a

ll it does it pop up the Msgbox line "Open" with my exact file name

That is all it is meant to do. I don't like to open files or change / erase data on other people's machines.

You need to replace the line that reads:

'Open file here

With some additional (conditional or unconditional) code to actually open the file.

Gary

 

[Gary McMaster]

being that it is the only file in that folder with BO in the title

Keep in mind that these sample are based on the above. If there is more than one file starting with "BO" it may or may not work. The first sample will return the first file it finds and the second will return the last file it finds. If there is only one, both will work. VBA help will tell you that "Dir" returns files "in no particular order". There must be some logic to it, dates, position on disk, file size etc but they don't say what that logic is. If there can be more than one file starting with "BO" it needs more work.

You might also have to check to see if that file is already open. You might also want to ask the user to confirm the open in case it was an errant click.

To unconditionally open it you could replace 'Open file here with:

Application.Workbooks.Open sBOFile

Gary

 

[Littlemalky]

Yeah that's the only file with BO in it within this folder. I tried workbooks.open sBOFile earlier and got a run-time error saying it couldn't find the file, i also tried your suggestion of application.workbooks.open, and still got the same runtime error.

 

[Gary McMaster]

Did you include the filename on the end?

Like this:

Application.Workbooks.Open sBOFile

Gary

 

[Littlemalky]

Yeah, it's strange because in the error it refers to the actual file name perfectly, but then states something is wrong in the name...

"'BO 1st Q 2011.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct."

The path is correct, it's "\\COBALT\Mfg Reports\Morning Meeting Notes\Production Daily Meeting\"