CODEWITHSUNDEEP
excelexternalfetchvba
ch-pub
ch-pub

Reputation: 1734

VBA for Excel: Server Data Not Updating

I'm using an Excel Add-in which fetches data from some server. I'm building a simple macro to fetch the data, delimit it, and format it. However, the macro does not fetch the data from the server.

The formula is as follows: =RTD([Server IP Address],"ALLASSETTICKERINFO")

When I type this formula directly into a cell, the data is returned. When I use this in a macro, the data is not updated.

Sub Update_Asset_Info()

Range("A2").Select

' this should be fetching data from a server. Instead it's returning a string "ALLASSETTICKERINFO"
ActiveCell.FormulaR1C1 = "=RTD([Server IP Address],"ALLASSETTICKERINFO")" 

' these "resfresh and wait" operations don't seem to help
Range("A2").Select
Calculate 
Application.Wait (Now + TimeValue("0:00:03"))


' data is fetched from server if the following code is omitted, but I can't omit it.
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
    TrailingMinusNumbers:=True
End Sub

The only way the data becomes updated is if the macro is terminated immediately after the formula is executed. If I try to format the data before terminating the macro, instead no data is fetched from the server. Rather, the cell will just say, "ALLASSETTICKERINFO". Even entering Wait and Refresh commands does not seem to help. Any idea how to get around this?

Upvotes: 2

Views: 523

Answers (1)

WGS
WGS

Reputation: 14169

After cleaning up your code, it can be reduced to the following:

Sub Clark_Update_Asset_Info()

Dim MainCell As Range
Set MainCell = ThisWorkbook.Sheets("Sheet1").Range("A2") 'Modify as necessary.

Application.Calculation = xlCalculationManual

With MainCell
    .Formula = "=RTD([Server IP Address],""ALLASSETTICKERINFO"")"
    .Copy
    .PasteSpecial xlPasteValues
    .TextToColumns Destination:=MainCell, DataType:=xlDelimited, Semicolon:=True
End With

With Application
    .CutCopyMode = False
    .Calculation = xlCalculationAutomatic
End With

End Sub

Please test the above code in a copy of your workbook in question as I don't think there are any glaring issues. However, some of the many things I see that can cause errors are that you use .Selection and .ActiveCell and that you forgot to use double-quotes for the .Formula input.

Let us know if this helps.

EDIT: Also, based on this Microsoft Support nugget, the syntax is of the form:

=RTD(RealTimeServerProgID,ServerName,Topic1,[Topic2], ...)

If this is the relevant function you need, I am seeing a required ProgID, ServerName, and Topic1 based on the above article. Mayhaps this contributes to the error as well? I can only see two things being passed to your RTD.

Upvotes: 1

Related Questions