json

  1. I

    How to edit this VBA line to an array for better optimization?

    This full code pulls data from a work website into Excel. Can be up to 55000 rows. Currently on Excel 2019 it takes forever to run. Also uses between 33%-50% of cpu. On 2016 it worked fine. I use a code already that turns off all things like calculations, events, etc. I am thinking an array...
  2. I

    VBA code only runs fast in a newly opened workbook

    I have this extremely strange issue with one of my VBA script JSON codes where it only runs fast (about 1 min and 20 seconds) when I open up a new workbook and insert my code and add Microsoft script reference right there. However, besides this it takes forever to run. Also if I save this file...
  3. I

    Why does this JSON Microsoft Script VBA code work in Excel 2016 but takes forever in 2019?

    I have this JSON Microsoft Scripting VBA code that pulls data from a work website into Excel. Can pull about 55,000 rows of data. About 5 columns of data. On 2016 the code worked fine but on 2019, which I need the code on, it takes forever to run or crashes Excel. I then have to quit Excel. I...
  4. I

    How to edit this JSON code to pull different data from web?

    Hi, so I have this JSON code that pulls metrics from a work website. However, it pulls different numbers than I am looking for. There are metrics on the web page in 60 minute increments and 15 minute increments. I need the 15 ones but I cannot figure out how to tweak this code to do it. Here is...
  5. T

    PowerQuery Error after reopening workbook "Authorization Error: The credentials for the source Web invalid."

    Hi All, I'd like to get the spotify web api data from the following link thru Excel PowerQuery web connector: Spotify API link I created a web query with the following code: let Bron = Json.Document(Web.Contents(functionGetNamedRange("SourceURL"), [Headers=[Accept="application/json"...
  6. Seba Robles

    JSON to CSV - Repeat all row items

    Hello, I have a JSON file that I converted to CSV using an online converter. When I open the CSV file, I have all the data but some rows have blank cells that I want filled. (See image below) Notice how rows 2 through 8, and row 14 are single row items. However, rows 9 through 10 and the...
  7. smozgur

    Using Power Query in Excel to Translate Data Using the Google Translation API

    When I started reading Jeffrey McManus' Database Access with Visual Basic book decades ago, I was very much aware that data was everything. I kept saying "You have a project only if you have a data set to manage". However, back in those days, you were mostly limited with the data included in the...
  8. Blue1971

    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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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. :)
  14. 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...
  15. 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...
  16. 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...
  17. 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")...
  18. 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...
  19. 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...
  20. 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?

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top