-1

I need to upload data from a text file to Excel. It's JSON data. It's around 50kB. If I regularly upload it into a cell, Excel freezes. Is there a way to upload the contents of the file directly to a VBA variable?

Community
  • 1
  • 1
Totallama
  • 418
  • 3
  • 10
  • 26
  • So you want to read the raw JSON data into a variable? – SBF Nov 10 '16 at 15:06
  • Yes there is. Please have a look at the topic [How to ask questions on Stackoverflow](http://stackoverflow.com/help/how-to-ask) and provide us some code sample we can help you with. You can also have a look at the following links : [Parsing JSON in VBA](http://stackoverflow.com/questions/19360440/how-to-parse-json-with-vba-without-external-libraries) , [Read text file in VBA](http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba) – Pierre Chevallier Nov 10 '16 at 15:08
  • Yeah, exactly.. – Totallama Nov 10 '16 at 15:09
  • I am not asking about parsing. What I need is simply upload the JSON string from a text file and store it to in a variable. – Totallama Nov 10 '16 at 15:11

2 Answers2

1

Found it, the solution is here and it works:

http://analystcave.com/

Dim fileName As String, textData As String, textRow As String, fileNo As Integer
fileName = "C:\text.txt"
fileNo = FreeFile 'Get first free file number  

Open fileName For Input As #fileNo
Do While Not EOF(fileNo)
   Line Input #fileNo, textRow
   textData = textData & textRow
Loop
Close #fileNo
Totallama
  • 418
  • 3
  • 10
  • 26
0

You don't want to import from text to Excel but from JSON to Excel. If you have to do it once in a time you'll better use some free on-line tools to convert JSON to CSV. For example this one. Otherwise, if you want to build a tool to parse the JSON file into Excel I advise you to follow this tutorial:

Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("id")
Sheets(1).Cells(i, 2).Value = Item("name")
Sheets(1).Cells(i, 3).Value = Item("username")
Sheets(1).Cells(i, 4).Value = Item("email")
Sheets(1).Cells(i, 5).Value = Item("address")("city")
Sheets(1).Cells(i, 6).Value = Item("phone")
Sheets(1).Cells(i, 7).Value = Item("website")
Sheets(1).Cells(i, 8).Value = Item("company")("name")
i = i + 1
Next
MsgBox ("complete")
End Sub
Nicolaesse
  • 2,554
  • 12
  • 46
  • 71