asked on

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.

Patrick Matthews

>>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?

:)

mdemint1

ASKER

:) we are a small company and will never have >999 jobs in 1 year

bromy2004

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

mdemint1

ASKER

@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")...

bromy2004

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

mdemint1

ASKER

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

mdemint1

ASKER

@bromy2004:

Have you been able to look at this any more?

bromy2004

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?

ASKER CERTIFIED SOLUTION

bromy2004

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bromy2004

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

mdemint1

ASKER

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!