DIR search - opening multiple files

[AmandaCatRuth]

Excel 2010

I have a macro that searches through network folders and opens the "tracker" files for jobs. These are just Excel files. The tracker is based around a 6 digit job number (ex: 120001). The actual file name will include the job number and other text (usually a company name) example: 120001 Bob's Company

The code tracks through a bunch of folders (my company has them organized oddly) and opens the file.

SumPath = [path] & ProjFYear & " Project Info Files\" & YPath & "\"
TName = (ProjNum & "*" & ".xls")
TFile = Dir(SumPath & TName)
Tracker = SumPath & TFile
 
Application.EnableEvents = False
Workbooks.Open FileName:=SumPath & TFile

This works wonderfully...as long as there is only one file with that job number. 95% of the time, that's the case, but sometimes, I'll have two like this:

130001 Bob's Company
130001 Bob's Company (IRS)

In cases like these, ONLY the second file opens (the IRS one).

What I would like to do, at least, is open both files when this occurs.

Optimally (and I am not sure if this is possible), I'd like to have a pop-up box listing both file names, and allowing me to select whether to open one, the other, or both.

So, how do I do this?

Any help would be much appreciated.

 

[JLGWhiz]

Excel 2010

I have a macro that searches through network folders and opens the "tracker" files for jobs. These are just Excel files. The tracker is based around a 6 digit job number (ex: 120001). The actual file name will include the job number and other text (usually a company name) example: 120001 Bob's Company

The code tracks through a bunch of folders (my company has them organized oddly) and opens the file.

SumPath = [path] & ProjFYear & " Project Info Files\" & YPath & "\"
TName = (ProjNum & "*" & ".xls")
TFile = Dir(SumPath & TName)
Tracker = SumPath & TFile
 
Application.EnableEvents = False
Workbooks.Open FileName:=SumPath & TFile

This works wonderfully...as long as there is only one file with that job number. 95% of the time, that's the case, but sometimes, I'll have two like this:

130001 Bob's Company
130001 Bob's Company (IRS)

In cases like these, ONLY the second file opens (the IRS one).

What I would like to do, at least, is open both files when this occurs.

Optimally (and I am not sure if this is possible), I'd like to have a pop-up box listing both file names, and allowing me to select whether to open one, the other, or both.

So, how do I do this?

Any help would be much appreciated.

I'm not the brightest candle on the cake, but I se no reason why both files should not open with the current code, unless the file extension is different for one of them.

 

[patel45]

Sub jolly2() '
Dim wbOpen As Workbook
strPath = [path] & ProjFYear & " Project Info Files\" & YPath & "\" 
TName = (ProjNum & "*" & ".xls") 
TFile = strPath & TName 
Application.EnableEvents = False 
strExtension = Dir(Tfile)
Count = 0
Do While strExtension <> ""
   Count = Count + 1
     strExtension = Dir
Loop
If Count = 1 Then
      Set wbOpen = Workbooks.Open(strPath & strExtension)
Else
      With Application.FileDialog(msoFileDialogOpen)
             .InitialFileName = strPath & "3*.xls*"
             .Title = "Please Select a File"
             .ButtonName = "Open"
             .AllowMultiSelect = False
             .Show
             If .SelectedItems.Count = 0 Then Exit Sub            ' User clicked cancel: Exit
             strFile = .SelectedItems(1)
      End With
      Set wbOpen = Workbooks.Open fileName:=strFile
End If
End Sub

 

[AmandaCatRuth]

      End With
    Set wbOpen = Workbooks.Open fileName:=strFile
End If
End Sub

The "set wbOpen = " line turns red and errors upon pasting in that code. I get a message:

Compile error: Expected: end of statement

Also: Why the change from my "sumpath" to "strpath"? Aesthetics or required for the rest of the code?

 

[patel45]

I'm sorry, the correct line is

Set wbOpen = Workbooks.Open(strFile)

I changed because I proposed a my old code

 

[AmandaCatRuth]

The first time I run the code, it tracks to the correct location and I get the "select a file" message, but there's no files listed.

I hit cancel, at which point the code stops working until I restart Excel - the message says the location could not be found. The location is correct up through sumpath.

 

[patel45]

Sub jolly2() '
Dim wbOpen As Workbook
strPath = [path] & ProjFYear & " Project Info Files\" & YPath & "\" 
TName = (ProjNum & "*" & ".xls") 
TFile = strPath & TName 
Application.EnableEvents = False 
strExtension = Dir(Tfile)
Count = 0
Do While strExtension <> ""
   Count = Count + 1
     strExtension = Dir
Loop
If Count = 1 Then
      Set wbOpen = Workbooks.Open(strPath & strExtension)
Else
      With Application.FileDialog(msoFileDialogOpen)
             .InitialFileName = [COLOR=#ff0000]Tfile[/COLOR]
             .Title = "Please Select a File"
             .ButtonName = "Open"
             .AllowMultiSelect = False
             .Show
             If .SelectedItems.Count = 0 Then Exit Sub            ' User clicked cancel: Exit
             strFile = .SelectedItems(1)
      End With
      Set wbOpen = Workbooks.Open[COLOR=#ff0000](strFile)[/COLOR]
End If
End Sub

 

[AmandaCatRuth]

I made those changes and the "Open" window brought me to "My Documents".

I changed

.InitialFileName = Sumpath & Projnum

Now it's going to the correct place, and searches for only files with that job number. (Having it as "TFile" was making it search for other instances of the first "Dir" i.e. (120001 Bob's Company (IRS) which was my original problem.)

So now it's working perfectly for multiple results.

How do I make it skip that extra step if there's only one match (which is most of the time)? If there's just one, I'd like it to just open the file.

Thank you SO SO much for all your help with this.

 

[patel45]

I don't know now the code you are using

 

[AmandaCatRuth]

SumPath = [path] & ProjFYear & " Project Info Files\" & YPath & "\"
TName = (ProjNum & "*" & ".xls")
TFile = Dir(SumPath & TName)
Application.EnableEvents = False
strExtension = Dir(TFile)
Count = 0
Do While strExtension <> ""
   Count = Count + 1
     strExtension = Dir
Loop
If Count = 1 Then
      Set wbopen = Workbooks.Open(SumPath & strExtension)
Else
      With Application.FileDialog(msoFileDialogOpen)
             .InitialFileName = SumPath & ProjNum
             .Title = "Please Select a File"
             .ButtonName = "Open"
             .AllowMultiSelect = False
             .Show
             If .SelectedItems.Count = 0 Then Exit Sub            ' User clicked cancel: Exit
             strFile = .SelectedItems(1)
      End With
      Set wbopen = Workbooks.Open(strFile)
End If
End Sub

The first half of the macro is dedicated to getting the "ProjNum" string, which is the 6 digit project number I mentioned in my first post. I didn't include that as it is spammy and not relevant.