How to get the drive letter of a portable HDD to update to an excel spreadsheet?

William Powell

Sorry if this is the wrong place so mods please feel free to move.

May be a simple problem but I can't find a solution.

I want to be able to open an excel spreadsheet and have one cell update with the drive letter of the drive where the spreadsheet is located. The spreadsheet is in the root folder on a portable HDD and holds a list of all the files on the drive with notes and details. Its set so the drive is Q: and I have hyperlinks so I can look up a file in the spreadsheet and click a link to open the file or its container.

Now I want the hyperlinks to update so if the drive gets allocated a differnt drive letter the links still work.

The folder hyperlinks currently use the format HYPERLINK(CONCATENATE(A1,C1),C1) were A1 is Q and B1 is / and C1 is the name of the folder, an example hyperlink would then become Q://Apps where C1 is Apps.
Best option I have so far is to manually update A1 if it changes.

This is a work in progress and I might even change the whole thing to an html list so I have a web page with links but Excel is handy enough atm. What I have on the HDD are all the files I use for computer fixes, patches, updates etc etc and rather than downloading the same ones time and time again because I forgot what the files did or what they were called I'm keeping the lot on one external HDD. Another nice touch is a link to the original downloads for files (like AV) that change regularly so I can update them. Excel allows me to add, searchable catagories and notes so I can quickly find what I'm looking for. And yes I do have too much time;)

Thoie

Put this into A1

=LEFT(INFO("directory"))

If you want to get really clever, you can add that into the concatenate.

edit:
Just did a quick trial there - if I have filename.txt in cell A1, then in B1 I can put
=HYPERLINK(CONCATENATE(LEFT(INFO("directory"),3),A1), "Click to open")

William Powell

I'll do some more research but at first glance function =LEFT(INFO("directory")) is giving me the root of the default save location of excel files which is the my docs folder on the C: drive. I'm looking for the location of where the Excel file was/is when it's opened.

But thats a really good start as I've spent ages searching and not come up with anything.

Edit> Checked this and if I change the default Excel file location to another drive than thats the drive letter that gets returned.

Thoie

Boo. Try =CELL("filename") and see what that does?

William Powell

That seems to do the trick Q:\[MENU1.xls]Main
so =LEFT(CELL("filename")) gives me the drive letter

Thanks thats brilliant.

Thoie

William Powell wrote: »

That seems to do the trick Q:\[MENU1.xls]Main
so =LEFT(CELL("filename")) gives me the drive letter

Thanks thats brilliant.

Yay!

Now to get really fancy, add properties (such as keywords, comments and version numbers) to your files, then write a macro that will scan the entire drive at the press of a button, and populate the excel file for you

William Powell

Not got that far yet, mainly because I don't need to as I tend to only be updating or adding one file at a time.

All the main folder and file data came from using a DIR command with switches and the output piped to a file, then cut and pasted into Excel. Not that fast but better than typing it all out or cutting and pasting individual file names.

Adyx

Would it not have been easier to just assign the HDD its own permanent letter?

William Powell

Adyx wrote: »

Would it not have been easier to just assign the HDD its own permanent letter?

Thats what I did originally and even had a cell reference to the fixed drive letter making it easy to change. But the point is that the drive is used on lots of different systems as its full of service packs, updates, fixes, etc.' so I thought it would be a nice touch when I use it on a system that is already using the same drive that if it would still work automatically. And to be honest the only way I ever learn anything is to try and do it the difficult way.

Working out how to parse a directory and update the spreadsheet is now a totally unecessary but likely next option

Thoie

William Powell wrote: »

And to be honest the only way I ever learn anything is to try and do it the difficult way.

Working out how to parse a directory and update the spreadsheet is now a totally unecessary but likely next option

That's why I was suggesting the file update using file properties to fill out the comments/keywords. From what I gather, parsing the directory and updating the spreadsheet in itself isn't *that* difficult - the hard part is getting the extra file properties like comments and keywords in.