1

Original Problem:

Within Azure Data Studio, I have saved the results of a SQL query into Excel spreadsheet. When I bring up the spreadsheet, a cell containing a formula (a URL hyperlink) is showing as text to display as opposed to results of executing the formula (a named visibly clickable hyperlink).

Here's the first three rows of raw data from the spreadsheet:

sourceTableNameA sourceTableComposedKeyA sourceTableComposedKeyA2 sourceTableNameB sourceTableComposedKeyB
FCC_ASR_LI_20210202 =Hyperlink("https://maps.google.com/maps?q=28.537266254426,-97.794735431672","C.2655506") https://maps.google.com/maps?q=28.537266254426,-97.794735431672 F3GIS_20210209 =Hyperlink("https://maps.google.com/maps?q=28.537266254426,-97.794735431672","{F4C8C17C-0702-4B08-ABC5-7ABF0BD76BAA}")
FCC_ASR_LI_20210202 =Hyperlink("https://maps.google.com/maps?q=29.709928035737,-96.912009716035","C.616145") https://maps.google.com/maps?q=29.709928035737,-96.912009716035 F3GIS_20210209 =Hyperlink("https://maps.google.com/maps?q=29.709928035737,-96.912009716035","{7F77EAB2-3588-4023-921F-4C009FC91BDC}")

Solution A:

By clicking into the cell (ex: B2 above) and then pressing Return, the cell then turns into the results of the formula.

New Problem:

I have +20K of cells which I need to execute "Solution A". And manually executing "Solution A" +20K times is not an option.

Solution B (barely adequate):

After investing considerable time trying to fix this, I discovered a marginal solution. It is to select the contents of the column containing the formula-as-text, then search/replace the "=" character. While this does in fact convert the text into an actual formula for each cell, it leaves each cell looking like regular text instead of making it have the default appearance of a clickable link. This is causing me to have to then manually underline and select a different color for all of these cells.

Solution C (possible?):

My first thought was this is a job for an Excel Macro in VBA. And this is as close as I was able to get:

Public Sub Convert_To_Hyperlinks()
  Dim Cell As Range
  For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
    If Cell <> "" Then
      If Left(Cell, 1) = "=" Then
        Evaluate (Cell)
      Else
        ActiveSheet.Hyperlinks.Add Cell, Cell.Value
      End If
    End If
  Next
End Sub

This function works exactly as I need for cells (ex: C2 above) that contain a raw URL (i.e. the cell does not start with "=" character) which calls the ActiveSheet.Hyperlinks.Add Cell, Cell.Value for each cell giving the desired result. However, the code that detects if the cell (ex: B2 above) starts with "=" which calls Evaluate (Cell) doesn't work on the selected cells.

What VBA code can I use to replace Evaluate (Cell), if any, which would produce the same effect as Solution A?

chaotic3quilibrium
  • 5,661
  • 8
  • 53
  • 86
  • 1
    `Cell.value = Cell.value` ? Perhaps overkill but something like this? https://pastebin.com/dMtMz9Tx - specifically for your column B cases? – QHarr Feb 11 '21 at 20:43
  • That simple code bit, `Cell.value = Cell.value`, did the trick. If you will create an answer that just copies my code snippet above and places your fix into it, I'd be happy to accept your answer. Tysvm for such a great answer. – chaotic3quilibrium Feb 12 '21 at 01:27

1 Answers1

1

In projects I have worked on I have found the following is often sufficient:

Cell.Value = Cell.Value

The beauty is that you can apply to ranges in one line.

Applying to your code with the loop:

Public Sub Convert_To_Hyperlinks()
  Dim Cell As Range
  For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
    If Cell <> vbNullString Then
      If Left$(Cell, 1) = "=" Then
          Cell.Value = Cell.Value
      Else
          ActiveSheet.Hyperlinks.Add Cell, Cell.Value
      End If
    End If
  Next
End Sub
QHarr
  • 83,427
  • 12
  • 54
  • 101