Set Directory as a variable based on cell value

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
I am using Ken Puls code to list files as hyperlinks it finds in a Windows folder. It works great but i am trying to make some changes that better suits my environment. wher it currently show "C:\My Reports\" & list. I know this is wrong but is as close as i can get to it . Can anybody help ?

Code:
dim list as string
list = "range ("f4").value

Set ShellApp = CreateObject("Shell.Application"). _ 
        Browseforfolder(0, "Please choose a folder", 0, "c:\My Reports\" & list)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there,

Can you give a bit more information for me? Maybe post the complete code and let me know which part isn't working for you?

What's in cell F4?
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Hi there,

Can you give a bit more information for me? Maybe post the complete code and let me know which part isn't working for you?

What's in cell F4?


Sorry about the confusion. in cell f4 in want to enter the name of the folder it will use as the base folder to start from in the box that pops up. in my example i would put C:\My Report in cell F4. i have users that may need it to search different folders . this way i can make a variable out of cell F4 and don't have to change the code for each individuals setup.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Okay, so if you're driving after C:\Reports\MyReport\ what would be in F4?
  • C:\Reports\MyReport\
  • C:\Reports\MyReport
  • MyReport
The code you're working with is this article?
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
my fault , i am causing all the confusion. I am after C:\My Reports f4 would contain C:\My Reports.
yes it was the article you posted the link to.

here is the code i need to change. where it says "C:\\" i woud like to replace that with a variable that reads the value in cell F4.

Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\\")
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
No, no big deal. I just want to make sure I steer you right is all. :)

So here's the code I would change:
Code:
Set ShellApp = CreateObject("Shell.Application"). _
        Browseforfolder(0, "Please choose a folder", 0, Worksheets("Sheet1").Range("F4").Value)

You'll need to update the worksheet name, of course.

The version you were working with up top, because you coded the path in the BrowseForFolder call AND the variable, would have ended up like this: "c:\My Reports\c:\My Reports\"
 
Top