Open folder based on cell value

  • Thread starter Thread starter burl_h
  • Start date Start date
B

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
 
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")
 
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
Click to expand...
 
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")
 
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
 
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
Click to expand...


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.
 
Back
Top