To see less ads Register or Login ----- Daily Fantasy Sports games 18+

hyperlink or Macro?

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

hyperlink or Macro?

Post by barry »

I'm trying to think of a smart way to find folders on my work system. basically we have 100's of sites we store information about. each site has a specific code, I want a quick way to find the relevant folder.

my initial thought was a spreadsheet with a list of the codes on one column and a hyperlink to the folder in the next column.

all that would take would be a scroll down to the relevent code, and then a click.....BUT.....

is there a way in excel (via macro? (which I've never used)) to just type the code into a box and it'll open up the folder in a seperate window?

User avatar
WilBert
Dumbledore
Posts: 7767
Joined: 28 Dec 2009, 21:07
Location: 2nd in SP4's Ashes comp.

Re: hyperlink or Macro?

Post by WilBert »

I'd probably go with a list of the codes with hyperlinks to the folders next to them then record a simple macro button to filter and sort the list in the background then return the one the user wants.

Any security restrictions on some of the folders?

mike_d99
Grumpy Old Man
Posts: 2657
Joined: 13 Oct 2005, 18:50
Location: cut down like a vegetable by your hard kickin mother who's an animal
FS Record: 269th TFF 08/09, 11th TFFO 08/09, 49th TFFE 08/09, 50th Elephant 10/11, 92nd Sky Sports 10/11

Re: hyperlink or Macro?

Post by mike_d99 »

Not sure if this is entirely what you're looking for, but you could create a reference sheet with a list of the folder codes and the filepath for each one in the next column in a big list. The macro could then use this to populate a drop-down list from all the existing entries and open the relevant folder when selected. It might be a pain to create the list in the first place but once you've done so it'd be easy to maintain and add or amend existing entries and you wouldn't have to create any hyperlinks.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: hyperlink or Macro?

Post by Surprised »

Are all the files in the same directory or same root directory?

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: hyperlink or Macro?

Post by Surprised »

You can get the list of file names via via firefox.
Just type in the directory location in the address window (ie C:/users/barry)
This will give a full listing of that directory. Then copy this into excel and you have the listing with links so you can open the file.

User avatar
unc.si.
FISO Knight
Posts: 11775
Joined: 11 Oct 2010, 14:08
Location: Off to buy Loctite
FS Record: 'Loser' by Beck

Re: hyperlink or Macro?

Post by unc.si. »

You could use a user entry box to input the file name, look up the file path and then use a shell command to open the folder in Windows Explorer all in vba within Excel.

Or you could just use the searchbar and restrict the result to folders?

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: hyperlink or Macro?

Post by barry »

Thanks for all comments, I think this is what I was trying to say :lol:

mike_d99 wrote:Not sure if this is entirely what you're looking for, but you could create a reference sheet with a list of the folder codes and the filepath for each one in the next column in a big list. The macro could then use this to populate a drop-down list from all the existing entries and open the relevant folder when selected. It might be a pain to create the list in the first place but once you've done so it'd be easy to maintain and add or amend existing entries and you wouldn't have to create any hyperlinks.
it's effectively what I'm looking at doing first - column A would have a long list (2000 odd) codes and column B would have a link to the folder containing all the data.

then I could take it a step further by introducing the macro - I would need to learn how to do this or have someone do it for me.....

gonna be a long term piece of work this I think though.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: hyperlink or Macro?

Post by Surprised »

barry wrote:Thanks for all comments, I think this is what I was trying to say :lol:

mike_d99 wrote:Not sure if this is entirely what you're looking for, but you could create a reference sheet with a list of the folder codes and the filepath for each one in the next column in a big list. The macro could then use this to populate a drop-down list from all the existing entries and open the relevant folder when selected. It might be a pain to create the list in the first place but once you've done so it'd be easy to maintain and add or amend existing entries and you wouldn't have to create any hyperlinks.
it's effectively what I'm looking at doing first - column A would have a long list (2000 odd) codes and column B would have a link to the folder containing all the data.

then I could take it a step further by introducing the macro - I would need to learn how to do this or have someone do it for me.....

gonna be a long term piece of work this I think though.

Firefox can get the list of files and when copied into excel the hyperlink will already be there so you don't need to make it. All you will need to do is to add the codes. If these files are added to or removed or the name is changed than a macro would be needed to get the list or you just use the firefox method every week or so (it only takes a minute)

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: hyperlink or Macro?

Post by barry »

wont be able to use firefox at work. :evil:

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: hyperlink or Macro?

Post by Surprised »

barry wrote:wont be able to use firefox at work. :evil:

works on opera and chrome.
Might work on IE but not sure

or

1. Open Windows Explorer and select the source folder in the left pane.
2. Press Ctrl + A to select all items in the right pane.
3. Press and hold the Shift key, then right click on the selection.
4. From the context menu, choose "Copy as Path".
5. Paste the list into Excel.

then create the hyperlink by linking to the first cell with a file path and just copy down. Use the code as the "friendly name" if you wish

mike_d99
Grumpy Old Man
Posts: 2657
Joined: 13 Oct 2005, 18:50
Location: cut down like a vegetable by your hard kickin mother who's an animal
FS Record: 269th TFF 08/09, 11th TFFO 08/09, 49th TFFE 08/09, 50th Elephant 10/11, 92nd Sky Sports 10/11

Re: hyperlink or Macro?

Post by mike_d99 »

barry wrote:Thanks for all comments, I think this is what I was trying to say :lol:

mike_d99 wrote:Not sure if this is entirely what you're looking for, but you could create a reference sheet with a list of the folder codes and the filepath for each one in the next column in a big list. The macro could then use this to populate a drop-down list from all the existing entries and open the relevant folder when selected. It might be a pain to create the list in the first place but once you've done so it'd be easy to maintain and add or amend existing entries and you wouldn't have to create any hyperlinks.
it's effectively what I'm looking at doing first - column A would have a long list (2000 odd) codes and column B would have a link to the folder containing all the data.

then I could take it a step further by introducing the macro - I would need to learn how to do this or have someone do it for me.....

gonna be a long term piece of work this I think though.
The macro itself wouldn't be especially hard to create, well not compared to collating 2000 filepaths :lol:

It's a job that only needs doing once though - after that you could add or remove any amount of entries to the list and it would still work.

Are the folders all in a similar place or are they scattered around numerous locations?

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”