Results 1 to 8 of 8

Thread: Set Directory as a variable based on cell value

  1. #1

    Set Directory as a variable based on cell value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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:\\")

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    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\"
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    That did it ! Thanks so much Ken.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Happy to help!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •