+ Reply to Thread
Results 1 to 13 of 13

Select pre-defined path

  1. #1
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Select pre-defined path

    Hi,

    Me again, sorry for calling on your help again as I have searched and can't get the code to work?

    I have a userform that had a list box and when the user selects certain criteria it asks to link to a file in a certain path to create a hyperlink on the relevant sheet.

    The hyperlink works and so does the opening of a explorer box. The issue I have is that the files I need the user to select are in specific folders.

    I have the below code:

    Please Login or Register  to view this content.
    But I want to declare the path where the files can be selected and am unsure how and where to put it in the above code.

    Help!?

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Select pre-defined path

    Just before C = Application...., you could add:

    Please Login or Register  to view this content.
    Of course change the D: to the correct drive, and adjust the pathname which is just a random example.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Select pre-defined path

    Hi Arkadi,

    Thanks for your reply.

    Problem is that it is a network drive so I don't know what it is? So chDrive "D:" doesn't work

    The workbook is saved in the same folder on the same drive and I keep seeing thisWORKBOOK.Path when googling. Still none the wiser though...

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Select pre-defined path

    Ah. Well drop the chdrive line then and try
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Select pre-defined path

    This doesn't work either as it is opening some random folder in my C drive?

    Am I missing something?

    Please Login or Register  to view this content.
    Thanks for your patience Arkadi

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Select pre-defined path

    Right... if the location on the network wasn't the last one accessed by excel, then it would still fail without the ChDrive. Here is an option:

    At the top of the Module, before any sub:

    Please Login or Register  to view this content.
    Then replace the ChDir line with:

    Please Login or Register  to view this content.
    Last edited by Arkadi; 06-26-2018 at 01:17 PM.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Select pre-defined path

    Can't test this, but try
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Select pre-defined path

    Cheers...

    Will try again tomorrow.

    I have another challenge... controlling the mob at home 😉
    Last edited by Ratso; 06-26-2018 at 02:24 PM.

  9. #9
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Select pre-defined path

    Hi Arkadi,

    I seem to be getting an error?

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Select pre-defined path

    Hi,

    Fluff13 seems to have worked but I have taken into account Arkadi's comment
    if the location on the network wasn't the last one accessed by excel, then it would still fail without the ChDrive.
    So what I have done so far is the below:

    Please Login or Register  to view this content.
    I will try again later and see if it still works but so far so good.

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Select pre-defined path

    Fluff's solution should not be affected by "the last one accessed"... so his solution should resolve your issue I hope.
    I had considered his approach as an alternative to my attempts to make your method of selecting a file work, but before I got around to suggesting that he had taken care of that method

    I guess that means we don't have to keep trying to solve my original approach, but out of curiosity, what was the error you were getting? I ask because I am on 32 bit version of excel, and don't get any errors. More than once I have seen a line go red because the 32/64 bit version doesn't support the other's version of a line of code, but that has never interfered with it executing.

    I have also seen a variation of the function declaration that I don't believe will make a difference but could be tried (and yes I realize this is a moot point, it is more in case someone else ever searches this thread):

    Please Login or Register  to view this content.
    In which case you would use SetCurrentDirectory ThisWorkbook.Path (dropping the "A" from the function call)
    Last edited by Arkadi; 06-27-2018 at 07:24 AM.

  12. #12
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Select pre-defined path

    Hi Arkadi & Fluff13,

    Apologies for the delay but I have only just got back to work and my project unfortunately has hit the back burner.

    Arkadi I will follow up on your suggestion and try again to see if your further suggestion works. I will come back to you

    I will mark this as solved for now.

    Thanks for all your help.

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Select pre-defined path

    Thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Save Chart as jpg in a defined path
    By Mengo85 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-02-2016, 10:36 AM
  2. #value returned when using spefic file path in defined name
    By DJGreen in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-15-2015, 02:14 PM
  3. Sheets(i).Select gives App-defined or Obj-defined error
    By torrmundi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2013, 04:11 PM
  4. Specified folder path to select file path
    By JayEmTee91 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2012, 10:38 AM
  5. [SOLVED] Run Time 1004 - App Defined or Object defined error with String defined path structure
    By sarails in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2012, 09:38 PM
  6. User defined variables in a path reference
    By CaddyMatt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2012, 02:08 PM
  7. Path for File Saving Defined by USER
    By crapmind in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2007, 09:58 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1