Accessing Recent Files- Part 1- an Automation with AHK and Excel VBA Macro

I’m hopping in and out of one particular project that I may not actively work on for months. When I revisit it I have to go fossiking through all my files to figure out which were the most recent ones I was working in.

Although I could just use Explorer and order the files by date, a lot of times I’ve made a copy of the file, added other things or gone off and done a different design/draft option, so I still have to review the files to find the exact one I want to work on.

So objective

To have a place where I can:

1. store a list of files that I want to refer back to later
2. on clicking on a particular file in the list, will open up the file, and the program it relates to, if that is not active
3. be easy to access & use, so that Id use it frequently (I’m thinking hotkey here)
4. has a date so that it can be sorted in reverse order (latest at top)
5. Has a notes/description field so that I can describe what is important about that particular file, eg 2nd rev and main drg for fencing that’s been issued
6. Has a good search facility so that you can filter by file type and date. Maybe even project.
7. Needs to be quite fast in operation.

Productivity process conundrum

I’m adding this at the top of the article, in fact, I’d finished article and was reading other stuff when I thought about this.

Do I change my processes to something more efficient, or do I adapt tools to work to the way I work?

Well, as an old cumudgeon, in this case, what I have works for me (to an extent), and I want a way to manage it. I do not want to re-structure my workflow to a new method of storing and retrieving files. On reflection, I’ve worked for 10’s of organisations with their different file structures and none of them stood out.

So, in developing this process, I could have looked at the issue in a more cerebral way and:

1/ looked at different methods of storing/retrieving files that met my needs and then reorganised my system to deal with this.

2/ Say, this is how I work, how can I create a tool to support me so I can be a bit more efficient in my execution and workflow?

For this I’ve taken the 2nd path. There are instancves where I’ll look at the first path, and in fact, a lot of programs force you to work in a specific way, not necessarily your chosen iterative process, but their way. So at times building tools that are bespoke for the way you personally work is a valid solution.

Another good question is :

what process is a pain point, tedious and takes a lot of time? Can you automate it?

This is a good place to start when looking at trying to describe the steps in the process to see if some of them can be automated to reduse the irritation. You may only be able to automate part of the process and this may alleviate some of the tedium which would be the justification for doing it.

AutoHotKey Script to do the task- part 1

Start witha AutoHotKey script that I could save a file and its path to a separate file, with a date of when I saved the file (good for searching later) and also a description/note in the file so that you can add a reference or explanation about the file.

I initially got it to write to a .txt file, but when viewed it was hard to select the filename to use, so I changed it to a .csv extension so when opening it with the run, %filename% command in AUTOHOTKEY it would open Excel instead. As it was a comma deliminated file it put the filename in a separate cell

I could make the filename clickable using =HYPERLINK(link_location,friendly_name), so all I need to do is add some string characters before the file and it then becomes clickable, which will open that file (and hopefully the program that runs if (if you’ve assigned that filetype to a specific program- (see Win10, settings -default)))

Below it shows file open in excel with a couple of clickable links and column C is comments- currently I only have a fixed string for that.

Windows Explorer showing recent files & desktop.ini file

I googled ” comment in file metadata” and apparently prior to Win 10 there was a feature that allowed you to put some information in the file metadata area, a bit like an mp3 file that stores the artist, music type etc, I think they may be tags though.

Some files, like .docs seem to allow for comment in the file metadata, others don’t.

I’ve seen comments about making a desktop.ini file that allows you to modify the File Explorer viewer to allow you to see comments & tags , I’m not sure if you can make filetypes have a “comment” field added to them. See video below about desktop.ini files & HERE for documentation that looks pretty confusing. This site shows a bit of script that you can add “some sensible information” to and it would need to be tested to see how much info you can add.

Shortcut links to files allow for comments , create via AutoHotKey

you can add comments to shortcuts to files/folders, so that may be an alternative method of keeping a list of files, in a specific folder with shortcuts with Comments. This may be the way to go by creating AutoHotKey script that would make a link and put it in a folder somewhere with a comment field.

AutoHotKey Script to do the task- part 2

When you run a script it has “FileSelectFile, SelectedFile, 3, %StartingFolder%, TITLE: Open FILE” which brings up a pop-up box that will allow you to choose the file that you want to select and assign the file & path to a variable that you can then use.

One of the issues here is that you need to assign the directory of where its going to open to begin with, and if you are moving all over the computer, I’m thinking some AHK script files, Excel files & BIM files , these are all located in far distant places from each other in the directories.

The only method I’m using at the moment is to start in one directory I use a lot and then use QAP (Quick Access Popup) with Wheel Click to pop up a selection of other directories I can go to. This may need some extra thought

Using File Explorer, Right Mouse Click Context Menu & Copy Path

Another way is to add, copy path to your File Explorer context menu (HERE is a tutorial on how to add it) and then that will copy to clipboard, so you can then, after selecting the file, have its full path & file name added to the AHK sript file with the file append. This may be an easier way to go, first select file then run the script to add it to the list.

Pop-Up GUI to add description/comment to the file, auto date/time add

So there are a couple of methods of selecting the file & path, also the date and time can be easily added automatically to the line on the file, but you need some method to add a description/note about the file, maybe tags or other data and this will require a GUI of some sort, or else, after adding the date/time & file you could open the file up and edit it and add it there.

Editing the file might be easy, apart from, if you are opening Excel each time and that can be slow.

Editing in something like Notepad++ might be better but the problem is, if it’s a long line then it won’t do word wrap unless you set it up at the beginning, then you’ll be able to read the whole string and add/subtract from it.

So you could have 2 hotkeys in the script, one that would open the file in Excel and then easily order the files by latest date at top & click on file hyperlink, and the other that would open it in Notepad++ so that you could add a comment/note/description of your choice.

Rich Text Format filetype a bit of a rabbit hole

I thought I could make a clickable link to files in the text document, but apparently you can’t.

The idea being you open a document and edit it and have hyperlinks in it.

But supposidly you can in a .rtf (Rich Text Format) file, so I’ve been trying to do that using Wordpad. I’ve found some format script to add to the text for a link for URL but that doesn’t seem to work with a file link.

If you do a Object file link inside wordpad it gives an image as well and then that translates into lots of bits in the file on converting to .txt format, so that didn’t work either.

Worth exploring and may need to be revisited later.

I wonder if you make the doc a HTML file and put clickable links in that? You open in browser, could be worth exploring. You could just insert the text from the file into an HTML boilerplate code to create new file to read, unfortunately you couldn’t edit in it.

Opening with Excel online or Google Sheets

For Excel online. This isn’t good as it uploads .csv file to OneDrive and then can’t find the path on the PC for the files.

I thought it might be quicker than opening Excel on PC and then loading file, but not the case as the hyperlinks aren’t effective.

Google Sheets does not recognise the excel hyperlink and can’t go to a file on personal PC, (again, can go to file on a server) so this doesn’t work either.

HTML file open in browser.

Nope, this doesn’t work either, you’d need to locate the files on a server so that the link will work accessing them. If you get the fil it won’t trigger a program to open them. So another explore with poor results.

  FileToSaveHtml =<p>%DT%,<a href="file:///%SelectedFile%">Link 1</a>,%Note%</p>`n ; 
 
  FileAppend, %FileToSaveHtml%, %FileListHtml%

 <p>
  2021-09-03--12:33,
  <a
    href="file:///C:\Users\drake\Downloads\AHK-Working\FileRecent\RecentFilesHtml.txt"
    target="_explorer.exe"
    >Link Text</a
  >
  ,"NOTE HERE-----------"
</p>

When activating the link it just shows raw file.

Saving Shortcut links to a directory

Shortcut links can have comments, so you could build a shortcut link to a file and add a comment that you can read if you hover over the icon, or if you have explorer open with comments column

Although you could setup this process, it doesn’t appeal to me, soI didn’t pursue it.

AutoHotKey Script – part 3

So I can create files with the data I want, ether .txt, .csv, .html or .rtf and put the data in.

I can add a GUI to allow me to put notes into a string to add to the file selection.

I just can’t seem to create a file that I can look at with a link to go to that file and open it so far apart from with Excel ( I could do the same with Word).

Only process is opening Excel and then the file link will open. Tested on a .pdf and it opens Adobe and then the file. I’ll have to test it with other programs to see that different filetypes work with different programs that they are referenced to.

So its looking as if this tool is OK if you have excel open ready to fire up a file but can take a while to start if not. This reduces its effectiveness.

So it might be worthwhile to set up the script and a GUI to add notes about the file, then maybe have an excel VBA macro to bring it in and have filter buttons.

Use AHK for Stripping file name from path for Link text.

An issue is with creating the hyperlink, hiding the full path and only using the filename to select :

=hyperlink(" C:/blah.../file.txt" , "file.txt")

The “comma” in the middle of the formula moves the second part to a different cell in Excel when importing the CSV file, so it cannot be built easily in AutoHotKey script.

This would mean a AHK script to create the data , and a VBA macro build the hyperlink to search and activate. Not the elegant solution I was thinking of.

Excel VBA script

I already have a macro-enabled excel sheet for importing PDF data from 3D pdf’s for BIM information.

I tried copying the macro’s I’d want across into a new workbook but kept on getting bugs so in the end I just saved the existing Macro to a new worksheet and used that as basis for new macro-enabled excel workbook.

Macros:

1. Import csv file ( opens file dialogue to select file (actually preset to specific file). What is good about this is it copies data across so keeps original file intact.
2. Delete imported data, so can re-import later the latest file
3. Insert Hyperlinks in an empty cell in the row
4. Filters off
5. Filter- order by latest date (sets date column filter sort)
6. Filter by Extension, allows you to type in the extension you want to search for , eg exe for .exe files, .txt for .txt files etc, to simplify your search.
7. Copy the data to a new sheet.
8. take copied data to new sheet (item 7) and send it to a new excel file. You have to say which sheet you wish to copy and give it a new file name.

The only Macro that I had to build from scratch was the hyperlink in column 2. This I used MacroRecord in the developers tab and edited it. Still took me a day to set up the sheet.

Files to download

The files can be downloaded HERE.

There are 3 files:

1. the AHK script file – PasteRecentFiles.ahk that uses Ctrl + WinKey+ a as hotkey to activate selecting the script.
2. The “C:\Users\drake\Downloads\AHK-Working\FileRecent\RecentFiles.csv” file that the the script writes the file information to. Note!!!! You will have to change this file location in the PasteRecentFiles.ahk to a location on your own PC
3. The Excel Macro File FilesMacro.xlsm where the C:\Users\drake\Downloads\AHK-Working\FileRecent\RecentFiles.csv is imported into. Note , in a VBA macro GetCSVList() [see below] you will need to change the location of the .InitialFileName= to either the File , or the file location (either will work) to select the file that you want to import. You could have it as “C:\” but then you’ll maybe have to do a lot of steps to get to the directory where you are storing your RecentFiles.csv file.

Sub GetCSVList()
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
    .AllowMultiSelect = False
    ''Start in
    .InitialFileName = "C:\Users\drake\Downloads\AHK-Working\FileRecent\RecentFiles.csv"
    .Show
End With

For Each fname In dlgOpen.SelectedItems
    ImportCSV fname
Next
End Sub

End comment

This has been an interesting exploration about saving files with comments. It is not a straightforward process and I’ve had to use 2 different macro programs and Excel (although it could have been a “Word” doc as well).

And I’m using paid for software (Excel online will not run VBA macro’s for free version) to do , from what looks simple and obvious on the outside, of saving a list of files and attaching a note to them.

I can’t use Google Sheets as its browser based so will action a file on a server, but not one on your local PC.

.RTF files don’t open the program you need based on you Settings Default for filetypes so although that path looked interesting it didn’t really come to anything.

This is a solution, a bit rough and ready, a Proof of Concept (POC). You can elaborate on it by, in the AHK script adding a drop down menu to have a selection of popular directories in which you have files that you commonly want to register, like ongoing o=project directories.

My usual process when defining a problem and looking for a bit of code to do this task, is to look online to see who has done it precviously and what their solution was, but in this case I couldn’t find anything, which I thought was odd as it’s a pretty common problem. I didn’t find anything in AHK forum,so that was pretty unusual.

I do need to make a button to delete a line number from the file so that dead links are deleted, this is an important part of ths automated and usable tool.