Today while I was basically doing nothing but day dreaming, I hit upon a novel idea for an Excel macro that could be incredibly useful. We have a bunch of ID numbers at work and use them in multiple files. In one file it might have one set of information and in another it has slightly different information, plus the original set of information. There are cases when if you want to know something specific about that ID number, you need to locate the file that has the information you want in it, which also depends on you knowing that the file actually exists. Not great.
What if you could find all the informational details of any ID number quickly any file? Luckily, we use these ID numbers in Excel files and I know a fair bit of VBA, and so my solution was to chop and change some VBA code I found online to open a text file for an ID number when a particular key combination was used, which would have a brief summary of the information from whatever sources we have. The summary would be in a text file to save space and loading time.
With a little effort, I found some code to open a text file in Notepad:
Which is the first step taken care of, but what if the file doesn’t exist yet? I found this here:
Open "myFile.txt" For Output As #1 Write #1, "text to write" Write #1, "second line of text to write" Close #1
Which I could also use to set up some default text in the file as placeholders, I’d also need to check if the file existed or not before creating files of course, which I found a simple solution to here:
If Not Dir("C:\Temp\myFile.txt", vbDirectory) = vbNullString Then MsgBox "exists" Else MsgBox "does not exist" End If
And that was all I actually needed for the macro. I put that all together, saved it in my personal workbook, and linked it to the key combination CTRL + SHIFT + T.
Voila, any time I hit that key combo with a cell containing an ID number, it opens or creates a text file for me. If the file already exists, I can read any information that was put in before, including dates and a log of changes.
Logs in Notepad?
Yes, logs in Notepad. Something I’ve learned over the years is that if you can keep a log of all the changes you make, you will be far better equipped to handle surprises arising from prior changes. And one of the ways you can make logs extra useful is with date and time stamps.
Now, I vaguely remembered there was a way to get timestamps in Notepad, but I couldn’t remember how. A little searching soon sorted that out though and I hit a really useful article with everything I needed.
Just hitting F5 will insert the current time and date on the current line in Notepad. With that nifty trick up my sleeve, I decided to add the most important information in a summary at the top of the text file and keep a log below of every change I make to the summary data, including the first entry of the data.
I could also use .LOG at the beginning of the file to automatically insert the date and time every time the txt file is opened, but then it would always add the log at the bottom (I want the latest changes at the top) and I feel like someone would save the log date without adding any information, resulting in a long list of timestamps for no reason.
This has been quite a technical post, but I found it to be something that could be incredibly useful and wanted to share it while the idea is still fresh in my mind.
Imagine the possibilities if you could find out anything you needed to know with just an ID number or a code or a special word and a little keyboard shortcut. Imagine if you could give anyone that information without even having to be involved, just give them the macro and let them loose (back up your files!).
Now I just need to figure out a way to prepare all those files automatically…