Set Directory as a variable based on cell value

**tommyt61** · 2012-01-27, 04:56 AM

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** · 2012-01-27, 05:31 AM

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** · 2012-01-27, 05:38 AM

Originally Posted by Ken Puls

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** · 2012-01-27, 05:44 AM

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** · 2012-01-27, 05:49 AM

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** · 2012-01-27, 05:54 AM

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\"