Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open folder based on cell value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open folder based on cell value
Try...
Dim lRow As Long lRow = ActiveCell.Row Const sInitPath As String = "C:\Users\Public\Pictures\" '//main path 'Add subfolder[s] strpath = sInitPath & Cells(lRow, "B") & "\" & Cells(lRow, "C") strfile = Dir(strpath & "\*.jpg") -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open folder based on cell value
On Jan 28, 12:53*pm, GS wrote:
Try... * Dim lRow As Long * lRow = ActiveCell.Row * Const sInitPath As String = "C:\Users\Public\Pictures\" '//main path * 'Add subfolder[s] * 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open folder based on cell value
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") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open folder based on cell value
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[s] 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open folder based on cell value
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[s] 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Should be simple: Lookup a picture from a folder based on a cell value changing | Excel Programming | |||
User selection of folder and open all .xls files within folder | Excel Programming | |||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name! | Excel Programming | |||
save to folder based on cell value | Excel Discussion (Misc queries) | |||
open file from folder save in new folder | Excel Programming |