Excel doesn't evaluate function after getting data from SQL db

Question

I am getting data from SQL and loading it into a worksheet.  Some of the cells have text that contain a function call, like "=HYPERLINK("bbg://securities/A&ENET%205.5%2012|01|25%20Corp/DES","D")"

All it's trying to do is show the link as one character.  It should evaluate to "D".

However, after getting data, the cell shows the the entire text above.  To get the "D" to display, I have to edit each cell and hit ENTER.  I searched the web for a solution, but could not find one that worked.

Answer 1

Data is from a local SQL database.

Yes, the macro works, the problem is settled, but it runs slow.

Answer 2

getting data from a SQL view,

data > get from web ？

can you share the URL？

had to step thru each cell to get the HYPERLINK function result to show instead of the actual text in the cell

For Each c In Selection

    c.FormulaR1C1 = c.Value 

Next c 

have you settle the problem？

but running slowly？

Answer 3

I'm getting data from a SQL view, here's the macro. I had to step thru each cell to get the HYPERLINK function result to show instead of the actual text in the cell. This is pretty slow, hoping there's a better way.

Sub RefreshMasterView()

'

' RefreshMasterView Macro

'

'clear existing rows

Sheets("Master\_View").Select 

Rows("4:5000").Select 

Application.CutCopyMode = False 

Selection.ClearContents 

'get new rows from database

Range("A4").Select 

Application.CutCopyMode = False 

With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. \_ 

    Connections("Query - PRISM\_Master\_View"), Destination:=Range("$A$4")). \_ 

    TableObject 

    .RowNumbers = False 

    .PreserveFormatting = True 

    .PreserveColumnInfo = True 

    .RefreshStyle = 1 

    .AdjustColumnWidth = False 

    .ListObject.DisplayName = "Table\_PRISM\_Master\_View\_1" 

    .Refresh 

End With 

'sort by ticker and issuer

ActiveWorkbook.Worksheets("Master\_View").ListObjects( \_ 

    "Table\_PRISM\_Master\_View\_1").Sort.SortFields.Clear 

ActiveWorkbook.Worksheets("Master\_View").ListObjects( \_ 

    "Table\_PRISM\_Master\_View\_1").Sort.SortFields.Add2 Key:=Range( \_ 

    "Table\_PRISM\_Master\_View\_1[TICKER]"), SortOn:=xlSortOnValues, Order:= \_ 

    xlAscending, DataOption:=xlSortNormal 

ActiveWorkbook.Worksheets("Master\_View").ListObjects( \_ 

    "Table\_PRISM\_Master\_View\_1").Sort.SortFields.Add2 Key:=Range( \_ 

    "Table\_PRISM\_Master\_View\_1[ISSUER]"), SortOn:=xlSortOnValues, Order:= \_ 

    xlAscending, DataOption:=xlSortNormal 

With ActiveWorkbook.Worksheets("Master\_View").ListObjects( \_ 

    "Table\_PRISM\_Master\_View\_1").Sort 

    .Header = xlYes 

    .MatchCase = False 

    .Orientation = xlTopToBottom 

    .SortMethod = xlPinYin 

    .Apply 

End With 

'fix column A width that changed due to text "External Data 1: Getting...." showing in the cell while it was getting data. It should not have changed because .AdjustColumnWidth is FALSE.

Columns("A:A").ColumnWidth = 9 

'refresh hyperlink formulas in cols F-I because they do not refresh automatically after import.

Range("F5").Select 

Range(Selection, Selection.End(xlDown)).Select 

For Each c In Selection 

    c.FormulaR1C1 = c.Value 

Next c 

Range("G5").Select 

Range(Selection, Selection.End(xlDown)).Select 

For Each c In Selection 

    c.FormulaR1C1 = c.Value 

Next c 

Range("H5").Select 

Range(Selection, Selection.End(xlDown)).Select 

For Each c In Selection 

    c.FormulaR1C1 = c.Value 

Next c 

Range("I5").Select 

Range(Selection, Selection.End(xlDown)).Select 

For Each c In Selection 

    c.FormulaR1C1 = c.Value 

Next c 

'clear last column select and set focus on first cell

Range("A5").Select 

End Sub

Answer 4

am getting data from SQL and loading it into a worksheet.

how did you get data from sql？

ms query?

Answer 5

select the column

menu

data>split