Excel VBA - Bitcoin

Question

I'm trying to run this macro on Microsoft Excel, but every time I do, I get a

Run-time error '5'

What am I doing wrong? This is my first time doing something like this.

What's suppose to happen is that everytime I run the Keyboard Shortcut, it's suppose to get the data from the blockchain website.

It's essentially just code that says..

1 USD is worth this many BTC

Sub USD_to_BTC()
'
' USD_to_BTC Macro
' Get the price of 1 btc if purchased by 1 USD
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://blockchain.info/tobtc?currency=USD&value=1", Destination:=Range( _
        "$D$8"))
        .CommandType = 0
        .Name = "tobtc?currency=USD&value=1_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

score -1 · Answer 1 · answered Mar 31 '17 at 19:38

-1

Remove (or comment out) .CommandType = 0 line.

As this MSDN Post explains,

You can set the CommandType property only if the value of the QueryType property for the query table or PivotTable cache is xlOLEDBQuery.

answered Mar 31 '17 at 19:38

BruceWayne

22,923
15
65
110

score -1 · Answer 2 · answered Mar 31 '17 at 19:39

There are two things going on here:

You are adding a query table and setting a variety of properties. One of those properties is CommandType, which is of constant type XlCmdType. You are setting that property to 0, which is not a valid value. You can simply comment out (or delete) that line of code. This will allow your code to run without error. However...
Every time you run this procedure, you will be adding a new query table. What I imagine you are looking to do is simply refresh the data. Once the Query Table has been added to your worksheet, you can simply run this code to refresh the data:
```
Sub RefreshQueryTable()

    ActiveSheet.QueryTables("tobtc?currency=USD&value=1_1").Refresh

End Sub
```

Keep in mind, if you are using a keyboard shortcut, a user might invoke the shortcut from any sheet in the workbook, thus this code will fail if the user is not on the sheet that contains the Query Table. You'd want to make sure you explicitly refer to the appropriate sheet:

Sub RefreshQueryTable()
    ThisWorkbook.Sheets("Sheet1").QueryTables("tobtc?currency=USD&value=1_1").Refresh
End Sub

Do I add that whole 3 line you just mention underneath my entire code? Sorry, I'm new to VBA. Trying to figure out where to add that in my code so it doesn't give me a error 9 issue. — user6260366, Mar 31 '17 at 20:06
You should first apply BruceWayne's solution to fix your error (what I've mentioned above in point #1. But if your query table already exists in the worksheet, you can add my 3 lines of code underneath your entire code. And make sure to update your keyboard shortcut to call RefreshQueryTable instead of your method. — Matt M, Apr 03 '17 at 13:32

score -1 · Answer 3 · answered Mar 31 '17 at 23:18

Try to use XHR instead of QueryTable:

Sub USD_to_BTC()

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://blockchain.info/tobtc?currency=USD&value=1", False
        .Send
        Range("D8").Value = .responseText
    End With

End Sub

Excel VBA - Bitcoin

3 Answers3