Open folder based on cell value

[burl_h]

first I have set up folders with pictures that i need to bring into
the spreadsheet. to do this i want to open the correct folder. The
folder to open will be based on two things,

first the folder name based on the content of column B in the active
row.
second a folder within the first folder named as the date that is in
column C in the active row

ex: column B (in active row) might say something like 123456 and
column C (in active row) might have a date as 1/20/12. so the folder
to open would be 123456\01-20-12

The key thing here is column B and C are fixed whereas the row number
has to be the current active row

The entire folder string for example would be similar to the
following:-
C:\users\public\picture\123456\01-20-12

would anyone have a vba solution for this

currently i'm using the following code, but it's not dynamic.


strpath = "C:\Users\Public\Pictures\123456\01-20-12\"

strfile = Dir(strpath & "*.jpg")

Thanks to all
burl_h

 

[GS]

Try...

Dim lRow As Long
lRow = ActiveCell.Row
Const sInitPath As String = "C:\Users\Public\Pictures\" '//main path
'Add subfolder
strpath = sInitPath & Cells(lRow, "B") & "\" & Cells(lRow, "C")

strfile = Dir(strpath & "\*.jpg")

 

[Don Guillett]

Try...

  Dim lRow As Long
  lRow = ActiveCell.Row
  Const sInitPath As String = "C:\Users\Public\Pictures\" '//main path
  'Add subfolder
  strpath = sInitPath & Cells(lRow, "B") & "\" & Cells(lRow, "C")

  strfile = Dir(strpath & "\*.jpg")

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

 

[burl_h]

I had to make one modification to make this work, I changed

strpath = sInitPath & Cells(lRow, "B") & "\" & Cells(lRow, "C")

to

strpath = sInitPath & Cells(lRow, "B") & "\" & Cells(lRow, "C") & "\"


One other slight problem, the cells in column C are date formatted
month/day/year
The folders are formatted as month-day-year example: 01-20-12
This cause conflict, how can the "/" in date format be substituted
with "-" in the folder name convention

Thanks
Burl_h

strfile = Dir(strpath & "\*.jpg")

 

[burl_h]

I just figured it out (how to chnage the date "month/day/year" to
"month-day-year" the following works nicely

Dim lRow As Long
lRow = ActiveCell.Row
Const sInitPath As String = "C:\Users\Public\Pictures\" '//main path
'Add subfolder
strpath = sInitPath & Cells(lRow, "B") & "\" & Format(Cells(lRow,
"C").Value, "MM-DD-YY") & "\"

strfile = Dir(strpath & "\*.jpg")

Garry, thanks for all your help.

Burl_h

 

[GS]

burl_h wrote on 1/28/2012 :

I just figured it out (how to chnage the date "month/day/year" to
"month-day-year" the following works nicely

Dim lRow As Long
lRow = ActiveCell.Row
Const sInitPath As String = "C:\Users\Public\Pictures\" '//main path
'Add subfolder
strpath = sInitPath & Cells(lRow, "B") & "\" & Format(Cells(lRow,
"C").Value, "MM-DD-YY") & "\"

strfile = Dir(strpath & "\*.jpg")

Garry, thanks for all your help.

Burl_h

You're welcome! However, if you persist to add the trailing backslash
on the path string then you should remove it from the start of the file
extension string. I deliberately put it here because paths normally do
not contain the trailing backslash.