Excel Tip – Finding and Breaking External References in Documents

Every once in a while I come across a problem where I open an Excel document and am notified of external references, even though I’m certain the document doesn’t or shouldn’t contain any external references. A common message is:

This workbook contains links to one or more external sources that could be unsafe

In order to find these references in Excel 2016, click the “Data” tab at the top. Then, under the “Queries and Connections” section, choose “Edit Links.” From there, a dialog will pop up showing any links and allowing you to check the status of the links. If the links are truly broken, checking the status should confirm that.

To break the connection, you can simply choose “break” with the appropriate connection selected. Any cell where a value was dependent on the connection was be converted to the current value, so you shouldn’t lose data by breaking the connection – it will simply no longer update along with the connected data source.

Resources:

 

Useful Gmail and Outlook Filters

Every once in awhile I get the urge to clean up emails and get rid of a bunch of stuff I just don’t need. On one hand, it’s nice to be able to reference old emails, but on the other you risk exposing your personal information to potential evil-doers (hackers or even mining/advertiser information if you’re using a freemail account like Gmail). Do you really need those emails from 8 years ago? Probably not.

Anyway, regardless of the motivation, here are some useful filters that I use:

Gmail

Gmail filters listed below are all performed by typing the unbolded text from the bullets below into your search box. You can combine them in any way you like – just put a space between each filter

  • All unread email: label:unread
  • All emails sent or received prior to a specific date: before:2018/1/1
  • All emails without a label: -has:userlabels
  • All emails not in the inbox, sent, drafts, or chat folders: -in:inbox -in:drafts -in:sent -in:chat
  • All emails with attachments: has:attachment
  • All emails at least as large as a certain size (in bytes – example is about 5MB): size:5242880

Outlook

Outlook is a little bit different as it offers some pretty powerful features such as search folders, but filtering can still be performed by using the search box for most items

  • All unread email (multiple options):
    • Choose the folder you want
      • On the right side of the mail pane, there is a dropdown that defaults to “All.” You can select “Unread” from the dropdown to see only unread
    • Create a search folder
      • On the left pane (of the default view) that shows your email folders, scroll down to the bottom of the Data File/Account where you want to view unread messages
      • Look for a folder called “Search Folders”
        • Right click on “Search Folders” and choose “New Search Folder”
        • Select “Unread Mail” from the list in the popup window that opens and hit OK
    • Drag the new “Unread Mail” search folder into your favorites (Optional)
  • All emails received prior to a specific date: received:<2018/1/1
  • All emails sent prior to a specific date: sent:<2018/1/1
  • All emails with attachments: hasattachment:yes (or has:attachment)
  • All emails with an attachment matching a specific file extension: ext:jpg
  • All emails at least as large as a certain size (in bytes – example is about 5MB): messagesize:>=5 MB

Resources:

How to Map the Windows Desktop to a Different Folder

I recently setup a computer for a user who likes to store a lot of files on their desktop. However, this is bad if those files aren’t being backed up. I’m a big proponent of using OneDrive, especially since my organization uses Office 365 and they give you 1TB of storage for free for each user license you purchase.

I suggested the user move their desktop files to OneDrive to give them a backup and access from other places, and they mentioned how they are more comfortable with the desktop because they get used to where certain folders and files are organized visually.

It turns out that you can map any folder to the desktop, and it’s easy:

  1. Open Windows Explorer (Win + e)
  2. Right-Click Desktop and choose “Properties”
  3. Click the “Location” tab
  4. Type the location of the directory you want to be the “Desktop” (or click “Move” and browse to the folder)
  5. Click OK

That’s it.

For the user I mentioned above, I created a folder called “Desktop” in their OneDrive for Business folder and mapped the Desktop to that location. Now there are backups and they can use the desktop as they always have.

H/T to this post for the knowledge: Can you change the location of the Desktop folder in Windows?

Quick Tip: How to get a text list of files in a Windows Directory

Every once in a while I need to get a list of just the file names in a directory. Sometimes there are a LOT of files and it would be a pain in the ass to type them out or write a program to manipulate them in some way.

The quick way to do this in Windows (images below are for Windows 10) is as follows:

  1. Open a windows explorer window (shortcut: hit Win + e, where “Win” is the Windows key) and navigate to the folder containing the files you want to print out
  2. Hold the Shift key and right-click in the folder (don’t click on any actual files) and choose the option “Open Powershell Window here”

  3. In the powershell window that opens, type

    Get-ChildItem -name

That’s it! now it prints a list of file names that you can copy/paste from the powershell window into something else (Excel, notepad, word, whatever)

Alternate method: command prompt

  1. Open a command prompt (Hit the Windows key, type “cmd” and hit enter)
  2. Navigate to the folder containing the images (type cd “<your file path>”, for example: “C:\Users\Public\Public Pictures\Sample Pictures”)
  3. Type the command below:

    dir /b

  4. If you’re using Windows 10, you can simply copy/paste from the command prompt. With an older version of windows, you need to:
    1. Right click in the command window and choose “Select All”
    2. Hit the Enter key. This will copy the contents of the command prompt to your paste buffer so you can paste using ctrl + v or right-click -> Paste

 

Connecting to Synology DiskStation from Windows 10

I’ve owned a small Synology Diskstation for a few years and really love its features and capabilities, especially considering its cost. One of the primary roles of my DiskStation is to backup my home computers. After recently purchasing a Surface Pro 4 and applying the Creators’ update, I was having trouble connecting to my DiskStation running DiskStation 6.1. After reading quite a few posts online about different problems, it seems the solution I needed was really quite basic.

What was most curious about this problem was that I could not see my DiskStation appear under “Network” under Windows explorer, and I received error code 53 (system error 53 has occurred. The network path was not found) when I tried to map the network drive using the command prompt like so:

net use T: \\DiskStation

Performing nbtstat -c from the command line and net view both listed my DiskStation with the UNC I was expecting, and the correct IP (I have mine configured as a static) in the case of the nbtstat command.

First, I made sure the SMB settings on the DiskStation were set to allow from SMB 1.0 to SMB 3.0 (DiskStation Control Panel -> File Services -> SMB -> Advanced Settings -> Maximum/Minimum SMB Protocol Settings).

Then, in Windows, if I opened Explorer and navigated to the IP address or the UNC sharename (\\DiskStation, for example), it would prompt me for a password. This was the primary point of failure for me earlier – I had forgotten that when logging into another server, whether it’s a Synology DiskStation or a Windows Server, you have to provide the server name AND the account name (or domain name/Account name, in the even that you’re connected to a domain).

So the Username wasn’t just “MyUsername” it was “DiskStation\MyUserName”. Once I did that, my DiskStation appeared under Network.

Converting a Lead Acid Battery-Powered Lawn Mower to Use Lithium Batteries

About 7 years ago, I was in the market for a new lawn mower. Looking at all the options at the time, I decided to go with an electric 24v, 20 amp-hour lawn mower sold under a brand called Earthwise. Here is the lawn mower in all its glory, model 60120:

Earthwise 60120 electric lawn mower

Credit: Amazon.com

I loved this mower from the get-go. It was extremely quiet, could mow my entire 1/4″ acre lawn in a single charge, and didn’t require gas, oil, spark plugs, etc. The only maintenance to do was charge the battery and sharpen the blade.

That entire first season was great, but it wouldn’t hold a charge for nearly as long the second season. I started having to charge it two times to finish my lawn by the fall. The third season was even worse. It wouldn’t hold a charge for more than a few minutes.

Opening the battery compartment on the lawn mower. 2, 12 volt batteries are wired in series to produce the 24 volts that power the mower.

I knew the batteries needed to be replaced, but I had no idea how much they would cost. I think I paid something like $150 for a replacement set, which is a pretty steep price. A few years later, those batteries were dead too. I gave up on it and bought a cheap used gas mower last year, but I hated using it. The pull starter was finicky, it would occasionally expel clouds of black smoke, and I would forget to buy gas for it from time to time.

I decided to look around and see if other people had found solutions, and sure enough they had. With the proliferation of lithium-ion batteries, it isn’t hard to find the batteries needed or to perform the upgrade.

What I needed to Do

In a nutshell, the task was simple. I had to do the following:

  • Buy lithium batteries to replace the lead-acid batteries
  • Cut the ends off of the black and white wires coming from the top of the battery case.
  • Solder new connectors to the black and white wires (whatever connectors matched the batteries I would buy)

It’s really that simple – just a few tasks and I would be on my way. A little research was required to figure out what batteries to buy, however.

Lithium Polymer (aka Li-Po, LiPo, or Li-Poly) Batteries

Lithium Polymer is a bit of a misnomer, since Lithium Polymer batteries are technically just lithium-ion batteries in a polymer casing (check out this excellent article for a good explanation on the difference between lithium-ion and lithium-polymer: Lithium Polymer vs Lithium-Ion batteries: What’s the deal?), but they came highly recommended as the battery of choice for this project. These batteries are being used all over the hobby world today, with drones leading the way. Lithium Polymer batteries are also used in many computers and cellphones.

Lithium Polymer batteries have a few important pieces of information written on them:

  • Voltage: You need a voltage that closely matches the mower. Since my lawn mower’s voltage is 24 volts, a 22.2v li-po battery is the best fit. Lithium polymer cells have a “nominal” voltage of 3.7v. Lithium polymer battery voltages are just multiples of 3.7v because they run multiple cells together to form a single battery. Therefore a 22.2v battery is really made up of 6 3.7v cells. Nominal voltage means the mid-range voltage because the cells run at 4.2v when fully charged and 3.2v when fully discharged. That means a 22.2v battery will output somewhere between 19.2v and 25.2v during the course of its run
  • Number of cells: Batteries will often have something like “6S” or “3S” printed on them. This corresponds to the number of cells in the battery. 6S = 6 cells = 22.2v. 3S = 3 cells = 11.1v.
  • Capacity/Runtime/Amp hours: Runtime is measured in mAh aka milliamp hours. A battery that has 5000 mAh has a runtime of 5 amp hours. Considering my mower had 20 amp hours, I want my batteries to try and match that if I want the same amount of runtime.
  • “C” Rating/Capacity Rating/Discharge rating: Batteries also list a C rating, which is used to determine the maximum load that a battery can safely sustain. 1C = the capacity of the battery. Therefore if a battery has 5 amp hours/5000mAh, 1C = 5amps. If a battery’s C rating is 40C, then the max is 200amps.

All of this is explained in much greater detail by this excellent article: A Guide to Understanding LiPo Batteries

Based on all this information, I knew I needed 22.2v batteries, and I wanted to get somewhere around 20 amp hours. I read from another resource that 20C was sufficient for others who did this project, so I figured I could do that or above. Looking online, I found the batteries to be fairly expensive. I settled on 2 pairs of these batteries (sold as 2 each): https://www.amazon.com/gp/product/B01AW7CKLW/ref=oh_aui_detailpage_o01_s00?ie=UTF8&psc=1 (22.2v, 4500mAh, 6S, 45C, Deans connector). Note that it says they come with XT-60 connectors, but the picture shows Deans connectors, which is what I received.

Deans Connectors

Deans connectors are apparently very common in the hobby world. I bought a pack of male plugs and a few splitters:

Soldering the ends was a little bit tricky as the connectors from the battery were fairly thick. I eventually got it right though, and the connections work fine.

Other Considerations

Charging the Batteries

You also need a charger for these batteries. Unfortunately, you have to charge them one at a time, so if you want 4 batteries like I have, you either want a multi-battery charger or you have to be a little patient.

Knowing when to Charge

It’s a good idea to get low-voltage indicators: https://www.amazon.com/gp/product/B003Y6E6IE/ref=od_aui_detailpages01?ie=UTF8&psc=1. If you put these on your batteries when you use them, they make a rather annoying sound when the voltage drops to the low threshold. This is important because your mower’s meter isn’t going to tell you when your charge is low. If you push a li-po battery too much, you can cause damage to the battery or it could explode. These things are loud enough that I can hear them while running the lawn mower.

Safely Storing and Transporting

Li-Pos are very flammable and difficult to put out. It is advised to buy a (relatively cheap) fireproof bag for storage and charging and that you charge using the “Storage” setting when you aren’t going to use them for a week or more. You should store them at room temperatures and it is advised you are present while charging due to the fire hazards. The fireproof bag I purchased is here: https://www.amazon.com/gp/product/B01H4QCZ4G/ref=oh_aui_detailpage_o00_s00?ie=UTF8&psc=1

Conclusion

The mower now holds a charge that is easily long enough to mow my entire lawn again. Li-Po batteries are supposed to last for 200-300 charges in good conditions, so I’m hoping to get several years out of this setup. Charging is a bit of a pain, but I tend to mow on the weekends, so I’m usually around long enough to charge all 4 of them (It takes a couple of hours to fully charge each battery).

The mower has enough power to mow at most of the height settings, but it struggles to mow at the lowest levels. This is fairly consistent with how the lead acid batteries performed as well – there just isn’t enough output to chew up thick grass that is significantly lower than the current height.

It turned out that doing this conversion was fairly easy, but not particularly cheap. All told, I bought the following:

  • 2 sets of 2 x 22.2v 4500mAh, 45C batteries – $110 each set ($220 total)
  • 2 battery low-voltage indicators – $5 each ($10 total)
  • Li-Po battery charger/balancer – $55
  • Fireproof bag (holds 4 batteries, came with 2 more low-voltage indicators) – $15

Together, that’s $300, which could buy a decent gas mower. However, I’m a nerd so I enjoyed the project.

Resources: