1. B

    Select JSON records as Excel table via Power Query

    I have a URL that points to a sample server that returns JSON records: https://sampleserver6.arcgisonline.com/arcgis/rest/services/LocalGovernment/Events/MapServer/0/query?where=1%3D1&outFields=*&f=pjson I want to use the JSON records in Excel 2016 as a table. I've attempted to do this by...
  2. A

    Saving content of individual cells within a column as separate JSON files

    Is there a way to create a collection of JSON Files in a folder based on the content of individual cells in a column in excel? So basically, I have a spreadsheet with a column starting with cell B12 to B26. Each cell contains a JSON script. I want a VBA that would convert each of these cells...
  3. R

    Unable to automatically pull data and perform custom modifications to a table from a query whose source is a dynamically changing link

    Hey there, I am trying to make excel get automatically get data from a link (however the link changes based on certain inputs like ticker symbol, time intervals, to/from time) instead of manually typing out the inputs into the link, putting it into power query, performing transformations, and...
  4. S

    Parse JSON into Excel

    Hi I have been using the Jsonconverter vba parser available via https://github.com/VBA-tools/VBA-JSON to extract the data from json files such as https://stats.europeantour.com/v1/season/2019/players/strokes-gained/off-the-tee. This has been very easy to set up and it works. However, the...
  5. S

    Spaces don't look like spaces?

    Hello, I am putting together a string, and using the print statement to output to a file. I have an equation in the cell that I want to print out. The equation results in a string. That string has general characters, and spaces in it, and looks fine in excel. I use the following to print to...
  6. P

    How to Clear a JSON file

    Does anybody know what the command is to reset / clear / refresh a JSON file before the file is loaded with another set of data? Thanks. :)
  7. P

    JSON file within VB

    Hello. I am loading up a JSON file with particular data and dumping it out to Excel. The process works, but when I try and grab the next set of data, I get an error. I am assuming the JSON file needs to be cleared/emptied so that the next 50 rows of data can be loaded. Does anybody have a...
  8. W

    Above my ability level :-( (JSON Parsing/ Editing with VBA)-- I have attached my attempt

    Hello All! Hope you can help because I think I'm in on more than I can handle on this one. I have linked 2 files. One is titled "xepg.json" and one titled "JSONCHANNELNUMBEREDIT.xlsm". My goal is to get a json file imported to excel as a table (columns and rows) that can be easily edited and...
  9. S

    Unable to parse JSON data in Excel.

    I'm having some trouble with parsing JSON data in VBA. I have tried all of the examples online but I'm still unable to solve the issue. What I have managed to do is pull the JSON data into excel in the original format using another VBA code that pulled in data from another website. I've...
  10. J

    VBA - How to convert JSON to Table ?

    Hi, i have the following which loads a webpage and extracts the JSON Sub JSON1() Dim username As String, password As String username = "xxx" password = "xxx" Dim xmlhttp As Object, myurl As String Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP") myurl = Range("C1")...
  11. J

    MSXML2.serverXMLHTTP : Adding Authentication + getting JSON

    Hi, i am experimenting with the following: Sub testXMLHTTP_VBA()Dim xmlhttp As Object, myurl As String Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP") myurl = "https://www.mrexcel.com/forum/excel-questions/" xmlhttp.Open "GET", myurl, False xmlhttp.Send MsgBox (xmlhttp.responseText) End...
  12. S

    Custom Theme

    Hi, I am have a Line and stacked column chart Visual with formatting. (Show Data labels for CUSTOMIZE SERIES etc). I need a JSON Theme file for using with other similar visuals. Sample file is available in the link https://drive.google.com/open?id=1UmilRTUm_LGfv-tRoIXyyVpv3mLTOPtV Request...
  13. J

    ie.Navigate save file to folder?

    Hi, I managed to login to site then navigate to specific URL which is a JSON After loading the page, a prompt comes up to open/save JSON Is there a way I can save this to a specific folder with VBA?
  14. B

    External data from dynamic URL?

    Hi everyone! I've been trying to establish a data connection to an online JSON file. This seems to work just fine, but part of the URL is a date, like so: https://www.sampleurl.com/api/file.json?id=25&start=2019-01-01&limit=2500 I'd like to be able to have that date automatically be today's...
  15. O

    Using VBA to convert JSON, with multi-level dictionaries

    Hi All, I am trying to use the NVD database JSON files (available to download from https://nvd.nist.gov/vuln/data-feeds - ) so I can construct a sheet of all the vulnerailities and then I can sort them in Excel. Yes, I realise that NVD does offer a search function so I could limit it to Oracle...
  16. Mrsbex

    VBA to obtain Dropbox folder location into Excel

    Hi All, I have a macro which worked beautifully on my PC to save a workbook to Dropbox. However, when a colleague tried to use it, it didn't work because Dropbox on their PC is not in the usual place. I have looked into obtaining the Dropbox directory from the json file but I don't know how to...
  17. N

    Outputting a JSON response to sheets.

    Hi, I am using VBA-Web to pull in some data in a json format via REST. Which works fine using Public Sub PullStaff()Dim Client As New WebClient Dim Request As New WebRequest Dim RequestResponse As WebResponse WebHelpers.EnableLogging = True Client.BaseUrl =...
  18. F

    VBA xml to json problem

    Hello, I have a problem with the MSXML2.XMLHTTP object in VBA. I need to send a message in json but the auth token has double quotes in it. So in order for those double quotes to arrive in the message a backslash is used. Now by parsing the xml to Json the xmlhttp object adds an extra...
  19. M

    API Help

    I have established an API question via Power Query and I am just mimic it through VBA. The JSON document that is returned has multiple drill downs that I need; however, I had to create a new connection each time to change the let statements. I think this is where my query is going really slow...
  20. I

    Excel sheets parsing in to JSON using VBA Macro

    Hi, Need to parse a excel sheet containing multiple sheets in it in to a JSON using VBA Macro. And during JSON conversion below should be achieved. 1. The file has multiple sheets. 2. Create a Macro button in a new sheet and call it as Refresh JSON. 3. On click of the above...

Some videos you may like

This Week's Hot Topics