Can I Use Excel VBA Code to Open Folders from a List of Jobs?

Our company has an excel project list that has a column for "Job Number", "Job Name", "Job Description", etc...

I want to create a button (or a dialog box perhaps) that would allow the user to either type in the job number or click on the cell containing the job number and then have that job's folder open in windows explorer.  I don't think there will be any user preference with either clicking or entering the number... so I guess whatever is easiest.

Note:
1) The project folder will either be located on our company's "X:\Projects\" or "Z:\Projects\" folders on our server.  Both main folders also contain sub-folders for the job year (i.e. "X:\Projects\2010\" for "10-###" job numbers".  Perhaps it would be nice to be able to select the drive from a drop-down list, and have the code identify the correct sub-folder using the first 2 digits of the job number.

2) The job numbers will not match the folder name exactly... BUT the folders do begin with the job number.  Ex. Job #: "10-001" might have a project folder named "10-001 EE" or "10-001 Experts Exchange".  Again, the "10-" designates the year and the remaining 3 digits designate the job number of that year.  There will always be 3 digits to the right of the "-", never more, never less.
mdemint1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
bromy2004Connect With a Mentor Commented:
This one works by Right-Clicking on the cell you want the folders for
i.e. if you right-click on a cell that has 10-001
It will look in both directories and open Job #1 in 2010
The range Constant (Add) is the address with your values
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Const Add As String = "$A$7:$A$1006"

If Not Intersect(Target, Range(Add)) Is Nothing And Target.Cells.Count = 1 Then
  Dim Paths()
  Dim i             As Integer
  Dim tmpPath       As String
  Dim MyYear        As Integer
  Dim fso           As New FileSystemObject
  Dim fsoFolder     As Folder
  Dim fsoSubFolder  As Folder
  
  'Get Year
  MyYear = 2000 + Left(Target.Value, 2)
  
  'Each Path to open
  Paths = Array("C:\Projects\", "D:\Projects\")
  
  'loop through all paths
  For i = LBound(Paths) To UBound(Paths)
    tmpPath = Paths(i) & Format(MyYear, "@") & "\"
    If fso.FolderExists(tmpPath) Then
      Set fsoFolder = fso.GetFolder(tmpPath)
      For Each fsoSubFolder In fsoFolder.SubFolders
        If Left(fsoSubFolder.Name, 6) = Format(Target.Value, Target.NumberFormat) Then
          Shell "explorer.exe " & fsoSubFolder.Path, 1
        End If
      Next fsoSubFolder
    End If
  Next i
  Set fso = Nothing
  Set fsoFolder = Nothing
  Set fsoSubFolder = Nothing
  
  'Cancel Right-Click
  Cancel = True
End If

End Sub

Open in new window

0
 
Patrick MatthewsCommented:
>>There will always be 3 digits to the right of the "-", never more, never less.

What happens when you have that banner year and sell the 1000th job?

:)
0
 
mdemint1Author Commented:
:)  we are a small company and will never have >999 jobs in 1 year
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bromy2004Commented:
I've come up with this macro

Insert into the Worksheet (in vba) that will have the data (See attached)

This uses what ever is in Cell A1 and will look through the subfolders specified.

If you would like it to be done via each row it would be best to supply a sample workbook with the format

Private Sub Worksheet_Change(ByVal Target As Range)
Const add As String = "$A$1"
If Not Intersect(Target, Range(add)) Is Nothing Then
  Dim Paths()
  Dim i As Integer
  Dim tmpPath As String
  Dim MyYear As Integer
  Dim fso As New FileSystemObject
  Dim fsoFolder As Folder
  Dim fsoSubFolder As Folder
  
  MyYear = year(Now)
  Paths = Array("C:\Projects\", "D:\Projects\")
  
  For i = LBound(Paths) To UBound(Paths)
    tmpPath = Paths(i) & Format(MyYear, "@") & "\"
    Set fsoFolder = fso.GetFolder(tmpPath)
    For Each fsoSubFolder In fsoFolder.SubFolders
    If Left(fsoSubFolder.Name, 6) = Right(MyYear, 2) & "-" & Format(Range(add).Value, Range(add).NumberFormat) Then
      Shell "explorer.exe " & fsoSubFolder.Path, 1
    End If
    Next fsoSubFolder
  Next i
  Set fso = Nothing
  Set fsoFolder = Nothing
  Set fsoSubFolder = Nothing
  Range(add).Select
End If


End Sub

Open in new window

EE-Open-Folders.xls
0
 
mdemint1Author Commented:
@bromy2004

This seems to be really close!

The code should not assume the current year, but base it off of the first 2 digits... and actually... how you wrote the code made me realize... it would probably be best to just search the two locations for the whole job number.  That is, don't even have it look up the year, maybe just have it look for "10-001" under the main project folders like you have it looking for "010".  Does this make sense?

Also, could you modify this to be linked to a command button?  And maybe change the criteria from "$A$1" to the current cell (so they can highlight the job number and click "Go to folder")...
0
 
bromy2004Commented:
If we don't specify the year it will open all of those project numbers in each year.
So if you wanted job 001 it would open all folders for each year, potentiallly lots.
We can have a command button, or even a specific cell.

I'll try put it together, but it would be better if you could supply a template. That way it matches exactly.
It doesn't need to have any information, just the headers and 1 line of dummy data
0
 
mdemint1Author Commented:
I apologize... what I meant was, rather than searching for job "001", can we look up "10-001"?  As in, remove the MyYear code.  When I posted the question, I didn't even consider this...

Each job number is specific to the year already, so "08-001" wouldn't be found anywhere but the C:\Projects\2008 and D:\Projects\2008 folders.  Maybe we could have a combo box that allows the user to select the drive, then enter the job number, then click a button to run the macro...

I've attached a sample worksheet
Project-List-Example-EE.xls
0
 
mdemint1Author Commented:
@bromy2004:

Have you been able to look at this any more?
0
 
bromy2004Commented:
Sorry for not getting back to you,
it slipped down my inbox :s

Would you need to specify the drive (C: D:) or would you want to open both?
0
 
bromy2004Commented:
This one does what you suggested, with the button/TextBox/ComboBox at the top
The Job folder (10-001) needs to be typed exactly with no trailing spaces.
the ComboBox values need to have the trailing backslash,
C:\Projects\ is ok
C:\Projects is not
Private Sub CommandButton1_Click()
Dim FSO     As New FileSystemObject
Dim Path    As String

Path = ComboBox1.Value & (2000 + Left(TextBox1.Text, 2)) & "\" & TextBox1.Text
If FSO.FolderExists(Path) Then
  Shell "explorer.exe " & Path, 1
End If
Set FSO = Nothing

End Sub

Open in new window

0
 
mdemint1Author Commented:
Thank you my friend.  Either solution works GREAT!

(note to others... if you get an error with vba recognizing FSO, go to Tools>References and make sure Microsoft Scripting Runtim is checked off.

Thanks again!
0
All Courses

From novice to tech pro — start learning today.