finding broken links in Excel

The Tip of the Night for May 8, 2015 concerned a macro that will check to see if links on an Excel spreadsheet are active.

The vba code below is an improvement. The code I posted about back in 2015 will generate a new worksheet in your spreadsheet with a list of the filepaths that don't work:

This vba code, posted here by Eawyne, creates a new spreadsheet which lists all of the links, and also indicates which cells the links appear in. [Be sure to use the version posted by Eawyne on 11/18/21 - not the one below it - it will give you incorrect results.]

Refer to column C to see which links do and do not exist. The linked to text appears in column H, and the cell of the original link is indicated in column B.

The code as written by Eawyne will stop after checking 1000 links. It can easily be modified by changing this line:

ReDim arr(1 To 1000, 1 To 9)

Increase the '1000' on this line of code to 9999, or whatever value you need.

It is also set to review links on multiple worksheets - something the older vba code did not do.

Public Sub CollectHyperlinks()

Dim Sht As Worksheet, Hl As Hyperlink, FSO As Object

Dim arr() As Variant, i As Long, Anchor As Object

Dim FileMsg As String, AnchorMsg As String

ReDim arr(1 To 1000, 1 To 9)

Set FSO = CreateObject("Scripting.FileSystemObject")

i = 1

arr(i, 1) = "Worksheet"

arr(i, 2) = "Hyperlink Anchor"

arr(i, 3) = "File"

arr(i, 4) = "Hyperlink Name"

arr(i, 5) = "Hyperlink Address"

arr(i, 6) = "SubAddress"

arr(i, 7) = "ScreenTip"

arr(i, 8) = "TextToDisplay"

arr(i, 9) = "EmailSubject"

For Each Sht In ThisWorkbook.Worksheets

For Each Hl In Sht.Hyperlinks

Set Anchor = Nothing

AnchorMsg = ""

FileMsg = ""

With Hl

If FSO.FileExists(.Address) Then FileMsg = "Exists"

On Error Resume Next

Set Anchor = .Range

If Not Anchor Is Nothing Then

AnchorMsg = Anchor.Address

Else

Set Anchor = .Shape

If Not Anchor Is Nothing Then

AnchorMsg = Anchor.Name

End If

End If

i = i + 1

arr(i, 1) = Sht.Name

arr(i, 2) = AnchorMsg

arr(i, 3) = FileMsg

arr(i, 4) = .Name

arr(i, 5) = .Address

arr(i, 6) = .SubAddress

arr(i, 7) = .ScreenTip

arr(i, 8) = .TextToDisplay

arr(i, 9) = .EmailSubject

On Error GoTo 0

End With

Next Hl

Next Sht

Application.ScreenUpdating = False

With Application.Workbooks.Add.Sheets(1)

.Range("A2").Select

ActiveWindow.FreezePanes = True

With .Rows("1:1")

.Interior.Color = 10837023

.Font.Color = RGB(255, 255, 255)

.Font.Bold = True

End With

.Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr

.Columns("A:I").Columns.AutoFit

End With

Application.ScreenUpdating = True

End Sub

District of Nebraska on theTaxation of Costs

The United States District Court for the District of Nebraska has published a guide on how to prepare a bill of costs under 28 U.S.C. § 1920, which allows a party who gets a verdict in their favor to be reimbursed for certain costs by the losing party. It's dated January 2020, and is available at: https://www.ned.uscourts.gov/internetDocs/info/taxation-of-costs.pdf . The Tip of the Night for May 6, 2021 discussed a Taxation of Costs guide prepared by the District of Massachusetts, and the Tip of the Night for June 21, 2021 reviewed one posted by the Eastern District of Washington. These are some key points in the Nebraska guide you will not find repeated in every discussion of the taxation of costs.

On page 7 the guide states that, "Electronic media support fees produced solely for the convenience of counsel (e.g., ASCII expenses, costs of realtime transcripts, condensed transcripts, scanning and hyperlinking, and rough drafts)" . . . may not be taxed. Some districts have different rules about reimbursement for realtime fees. For example, a guide on the taxation of costs prepared the District of South Dakota states that, "[t]he requesting party must provide an explanation as to why it was

necessary to have any transcript produced in an expedited manner, including Realtime trial/hearing transcript feeds.", even though it too instructs that, "[c]osts of expedited transcripts produced solely for the convenience of counsel" may not be taxed.

The Nebraska guide instructs that in compensating for a witness's travel expenses, the prevailing party should refer to rates listed at www.gsa.gov/mileage when the witness uses his or her own vehicle for a trip, and to www.gsa.gov/perdiem for the maximum that may be charged for a 'subsistence allowance' when the witness has an overnight stay for a trial. A witness driving a car to a courthouse is reimbursed at a rate of 70 cents per mile as of January 1, 2025. Surprisingly rates are listed for trips made by privately owned airplanes and motorcycles as well!

A witness who takes a commercial flight, or a train trip, must "utilize a common carrier at the most economical rate reasonably available". While the guide doesn't refer to it, note that the General Services Administration has another page, https://www.gsa.gov/travel/plan-a-trip/transportation-airfare-rates-pov-rates-etc/airfare-rates-city-pair-program, on which it lets you search for the cost of a flight between any two cities, but also note that these prices may be lower than a witness can obtain because the government contracts for discounted fares.

The per diem costs may vary widely depending on what city a witness stays in, and at what time of year. $342 in New York City in December, but only $165 in Lake Placid at the same time, but $238 in Lake Placid, and actually one dollar cheaper in New York City in July.

The guide states that the clerk will not tax, "Routine expenses for copies made for discovery purposes, including discovery costs associated with scanning or converting ESI to a useable format." This differs from the decision in Johns Manville Corp. v. Knauf Insulation, LLC, 15-cv-00531-RBJ-KLM, 2018 U.S. Dist. LEXIS 88189 (D. Colo. May 25, 2018), in which the District of Colorado held that § 1920(4) should cover the costs of scanning and conversion to the TIFF and PDF image formats, following the Third Circuit precedent in Race Tires Amer., Inc. v. Hoosier Racing Tire, Corp., 674 F.3d 158 (3d Cir. 2012). See the Tip of the Night for May 29, 2018.

Extracting text around a string in Excel

If you need to extract text from an Excel cell which is X number of characters before and Y number of characters after a given search term, you can use the MID and MAX formulas with a nested SEARCH formula.

A formula in this format:

=MID(A2,MAX(SEARCH("stole a",A2)-35,1),70)

. . . can be set to return the number of characters before the search string equal to the value given in the MAX formula (in this example 35) and the number of characters from the start of the search string moving to the right equal to roughly half the value given for the MID formula (in this example 70).

. . . the SEARCH formula finds the position in the cell containing text where the first character of the search term appears.

The MAX formula then gets the value of the SEARCH result minus the number entered in the complete formula. In this it is case 35.

When the result from the MAX formula is fed into the MID formula, it is used to determine the position where the extraction should begin, and how many characters after that point it should include.