Visual Basic Code to Find and Replace Multiple Hyperlinks

Tonight, with the help of Copilot, I was able to generate the below vba code which will go through an Excel workbook, finding and replacing multiple static hyperlinks (not entered with an active formula) with new hyperlinks.

The code is set to work on both links to network filepaths, and to web-based urls. However filepaths which reference a network drive letter, should be converted to full UNC file paths, which include the server name. So instead of "P:\Acme\Trial\Closing.pptx", the path should be entered as "\\dewey.local\chi07\AL\Usershare"

[If you need help finding the full UNC file path for files on your network enter the command, 'net use' in command prompt and it will generate a list of the server paths for each drive you're mapped to.

Adding find and replace pairs to the vba code is easy. The old path is simply preceded by 'linkMap.Add' and then the two paths are enclosed in quotes and separated with a comma.

I tested the code tonight with a few hundred urls and the workbook was updated almost instantly.

Sub ReplaceNetworkDriveHyperlinks()

Dim ws As Worksheet

Dim hl As Hyperlink

Dim linkMap As Object

Dim oldLink As Variant

' Create a dictionary to store old and new network path mappings

Set linkMap = CreateObject("Scripting.Dictionary")

' Add your old and new UNC path pairs here

linkMap.Add "\\Server1\Shared\Docs\Report1.pdf", "\\Server2\Archive\Docs\Report1.pdf"

linkMap.Add "\\Server1\Shared\Docs\Report2.pdf", "\\Server2\Archive\Docs\Report2.pdf"

linkMap.Add "\\Server1\Shared\Images\", "\\Server2\Archive\Images\"

' Loop through all worksheets

For Each ws In ThisWorkbook.Worksheets

' Loop through all hyperlinks in the worksheet

For Each hl In ws.Hyperlinks

For Each oldLink In linkMap.Keys

If InStr(1, hl.Address, oldLink, vbTextCompare) > 0 Then

' Replace the old part of the path with the new one

hl.Address = Replace(hl.Address, oldLink, linkMap(oldLink))

hl.TextToDisplay = Replace(hl.TextToDisplay, oldLink, linkMap(oldLink)) ' Optional

End If

Next oldLink

Next hl

Next ws

MsgBox "Network hyperlinks updated successfully!", vbInformation

End Sub

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. This policy is subject to change at any time.  The owner is not an attorney, and nothing posted on this site should be construed as legal advice.   Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.  

Setting Relativity to Stop Formatting Email .png Files Being Stripped Out As Attachments

It's very common to come across images which were embedded in one email message and have become stripped out and added as attachments to a message in a later part of the same thread.

These may have been logos used in the signature block for the initial email or other formatting details.

The attachments can be added as additional family members, but they contain content which will not need to be reviewed:

Attorneys will be annoyed to have to review documents without any meaningful content.

Keep in mind that a Relativity admin can be asked to set a processing profile for a production so that embedded images or objects are not stripped from emails or PowerPoint presentations, even when other children are set to be extracted.

If the options for 'MS Office embedded images'; or 'Email inline images' are checked off, image files such as .jpg, .bmp, or .png files will not be published as separate documents in the review set, nor will they be OCRed or have their text extracted. The 'MS Office embedded objects' option will prevent files embedded inside MS Office files (such as a PDF in a Word document) from being added as new documents or being OCRed or made subject to the text extraction process.

How embedded objects are handled can vary based on many factors. For example, an Excel file that is pasted into a Word document may be stored as both the spreadsheet and as an .emf image of the active worksheet.

Of course, an image embedded in an email may also have substantive content - such as a bar graph or screen grab of another document.

The 'Roll up image text' radio button will add the text of the attachment [assuming OCR is enabled] to the end of the searchable text for the parent document, after a dotted line listing the name of the image or object.

Using the 'Roll up image text' option can cut down the number of files in the workspace and significantly reduce costs.

Physical destruction of a laptop hard drive

Tonight I spent some time destroying two hard drives for an old laptop that I no longer use. Both were hard disk drives. I had previously backed up the data on the hard drives, and then wiped the drives, but I wanted to take the additional step of making whatever remained on them physically impossible to access.

After wiping the drives, I tried to recover data from them using recovery tools included with the Hiren BootCD (see here), just to confirm the wiping software had been effective.

The first step was to remove the battery, so any chance of an electric shock could be eliminated. Next using a precision screwdriver, I removed the covers for the two drives in the laptop, which were both secured inside with metal braces, which also had to be removed.

The drives each had a controller board on one side, and a metal plate on the opposite side.

I did not have the right tools to unscrew the metal plate, so I drilled through each drive from the side with the controller board.

Drilling one hole took several minutes with a no-frills Black & Decker drill. While I managed to put two deep holes in each drive, I was not able to penetrate past the metal plate case on the opposite side.

A lot of people make the mistake of ending their effort to physically destroy a drive at this point. However, it is possible for the platters in the HDDs to still be intact after holes like these have been drilled. See for example this video posted by @cfldriven in which he demonstrates how drives with large drill holes . . .

. . . still have platters from which data can be recovered:

I couldn't pry off the metal plate to reveal the platters on my drives, but I was able to open up the holes I drilled further with a screwdriver and confirm that the platters had been smashed.

It was possible to hear the pieces rattling around inside, and several small broken shards spilled out of the case.

Of course, the more common solid state drives used in most laptops manufactured in recent years don't have platters, and drilling several holes in them may leave some of the chips on which they store data undamaged.