1. Y

    VBA Macro, read range from Sheet2 not working

    Hi, I have this code for getting JSON data from a specifc URLs that are listed in Sheet2, column A. However, i keep getting an error on the "If" statement. Anyone know how to resolve this? Public Sub NEWURL() Dim https As Object, Json As Object, i As Integer, j As Integer Dim Item...
  2. Y

    VBA Macro to extract JSON data and post into cells

    Hi, I'm thinking that the error is because the url is an HTTPS? I'm getting mismatch type error. I'm trying to getting the JSON data from this URL: https://min-api.cryptocompare.com/data/price?fsym=ETH&tsyms=USD This is the code i'm using: Public Sub exceljson() Dim http As Object, JSON...
  3. C

    Help with Importing XML or JSON online

    It seems I can't find a straight answer anywhere, so I hope someone here is able to answer my question, or at least guide me in the right direction. I'd like to know if there's a way to import specific data from an online source, such as a json or xml file, have it automatically populate the...
  4. S

    Advanced JSON Data Query with Web API (URL parts?)

    Hello, I'm using a Web API to retrieve tables of JSON data successfully into my spreadsheet. In the Query Editor window, there's an "applied steps" sequential list, featuring the first step, "source". Source is the actual web URL of the API itself. The dialog box for source allows entry of the...
  5. K

    Add item to VBA dictionary with quotes

    Hello, I'm trying to add the key classificationLevels with a value of ["Sectors"] (yes, I need the quotes) to a dictionary When I try Body.Add "classificationLevels", "[""Sectors""]" I actually add the following with the extra ""s. ["Sectors"] I also tried Body.Add "classifiactionLevels"...
  6. H

    Looping and Parsing a JSON-request

    Dear all, I have an excel-file with in cells B1, C1, ... , LastColumn1 the name of certain stocks. <tbody> Details BTC ETH NEO LTC ... "LastColum" USD 50 </tbody> Next, I want to send a JSON request to get the Open, High, Low and Close price of these assets. An example can...
  7. C

    Shape JSON Data from web API

    I am struggling to extract the data from my JSON source (see below) [{"id":"TEST1","created_at":"2017-02-22 11:55:15 +0000","updated_at":"2017-02-22 12:09:56 +0000","created_by":"chris.k@b.com","fields":[{"id<wbr style="color: rgb(0, 0, 0); font-family: Consolas, "Courier New", monospace...
  8. C

    Missing menu option - import json

    Hi I'm using Excel 2016 the import from json is missing on the menu bar Data > New Query > From File (only shows from workbook, csv, xml, text or from folder) need 'from json' I have checked all the other menus undr new query. Also checked in Excel options > add ins etc and cannot spot...
  9. D

    JSON VBA parsing problem

    So I'm trying to scrape historic score data from masseyratings.com, and then parse it using a JSON VBA parser. I'm running into issues defining the JSON fields to pull out, I get a runtime error 13 type mismatch on the underlined line of code below. The JSON data for this query looks like this...
  10. N

    Import Json to Power BI

    I am trying to subset some information imported from a json into power bi. I tried the code in RStudio and it worked, but when I imported this code in Power BI, it didn't show any results. I converted the data into a data.frame, but that didn't work. can someone please help me. the code I used...
  11. M

    How to get last element of a JSON object in VBA

    { "data": { "trackings": [ { "tracking_number": "3A5V198427201", "shipment_pickup_date": "2016-10-31T23:49:00", "tag": "InTransit", "title": "3531", "checkpoints": [ { "created_at": "2016-11-01T16:09:23+00:00"...
  12. S

    Extract Characters from 1 string in separate cells

    I have a JSON text result from an API, given below. {"visit":"1","paid":"0","repeatPat":0} I need the numbers to be extracted in 3 separate cells. Please assist.
  13. A

    JSON to Date\Time format

    Hi, I am trying to convert the following 13 digit json number -> 1452002465000 to date/time. I am using this formula ->=D7/1000/86400. This is giving me 03/01/1946/ 12:28 PM. The time is correct but the date is wrong (should be 05/01/2016)
  14. S

    Transforming json with power query (mix of list and Text in a single column)

    Hi anvg/all, I'm facing another issue in one of my feeds. ThI'm facing another issue in one of my feeds. This is also due to mix of list and text value in a single column. Please note that this is related to...
  15. S

    Transforming json with power query (mix of list and record in a single column)

    I'm trying to transform a (I'm a newbie in power query transformation) json data. Our aim is to merge and transform a few json files to create a report. The json files are provided by different feed exposed by our web application. As part of learning, we tried to transform a single json file...
  16. A

    JSON into excel from vb string

    Good morning all, Im in a real pickle and in need of some help from some experts out there, Im trying to get a web API through vb(done) then get the data from it into cells. The data is returned in JSON, which is where im stuck. Its basically a Q&A database, each question has a ref no...
  17. A

    Need help adding code to VB

    Hi Experts, I have a excel spreadheet with around 60K addresses all around UK. I was finding for a solution that will receive these addresses as input and provide the output with the coresponding address GPS coordinates "Longtitudes and Lattitudes". I have found a VB code on stackoverflow that...
  18. G

    Get a value from Json object

    I dont know if this is possible in excel and need your advice. If I enter the below url in the browser,I get a Json object with a "is_verified" parameter either true or false. https://graph.facebook.com/fql?q=SELECT%20is_verified%20FROM%20page%20WHERE%20username=%22nokia%22 This is what i get...
  19. S

    JSON API to Excel

    Hi, I'm trading crypto's and want to build an Excel spreadsheet to monitor marketmovements for analyzing. I can do so by using the API offered by the exchange; https://api.mintpal.com/v1/market/trades/MINT/BTC. Now I have never used VBA and find it really hard to find any tutorials for...
  20. K

    Parse JSON with VBA

    Hi everybody, I would like to parse a json string in VBA using this library : VB JSON Parser Library for VB6 (BSD Licensed) - Visual Basic, JSON here is an example of the json string I receive from a Web Service and want to parse: { "total": [[10000,1]], "periods": [1399852800000]...

Some videos you may like

This Week's Hot Topics