Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    JSON format and VBA

    This is brand new territory, so basically I am asking for directions at this stage.

    The next phase of my project requires "communication" to log into and send data and numbers to a server.
    The data and numbers are from my project, this I can do and have ready, in excel or a txt file.

    But, the company that has that provision for their customers to automate a method via their own means,
    ( Excel etc) uses JSON format.
    The entire script is provided publicly for whoever needs to use it.
    My question is, does the company's server script in JSON format, does this go into VBA Module, or is there more to develop in another type of program that is not Excel VBA, but "talks" with JSON format ?
    To Illustrate,
    As a retired motor mechanic by trade, this would be termed as a "Adaptor Plate" if one wants to use a Ford Gearbox on a GMH motor.

    So, do I need a "adaptor plate" program between Excel VBA and JSON format to log into their server ?

    Hope this makes sense.

    Thanks

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    JSON is a data format, like CSV. You either write code to export in the format or use add-ins etc.
    This page has it in a VBA module: http://www.rwardell.com/rtw/projects/emitxml.htm
    This page has an online converter: http://stackoverflow.com/questions/1...t-data-to-json

    cheers, Paul

  3. The Following User Says Thank You to Paul T For This Useful Post:

    XPDiHard (2015-07-25)

  4. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Here is some additional material that may be of use to generate Json from Excel: http://ramblings.mcpher.com/Home/excelquirks/json
    Rui
    -------
    R4

  5. The Following User Says Thank You to ruirib For This Useful Post:

    XPDiHard (2015-07-25)

  6. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks Paul T
    I checked out the links, it has xml and stuff I am not familiar with, but thank you.

    My current project in the Excel Workbook uses VBA, the web query is VBA from that same company's public web page which is the data I use, mostly numbers.
    This does not need to be changed. The Owners of the server are not asking me or their clients to change anything I do.
    What has change, or changing is the way the company wants it's clients/customers to access the server.
    They have provided a specific script to do this, basically.
    There are fields to add my username/email/member number;, password and then a sessionID, whatever that is and I don't know where to get one from, except it's somewhere accessible.

    At the end of *my* processing, lets say on Sheet10, I have the required data they ask for, except this sessionID they also require, for now.

    So if my required details are on Sheet10, what else is required of me to do to let their provided script in JASON know what is the Ranges within Sheet10 ? How do each of the languages "talk" ?

  7. #5
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    ruirib , thanks.
    I'll just keep reading, might "get it"

  8. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by XPDiHard View Post
    ruirib , thanks.
    I'll just keep reading, might "get it"
    I fear I cannot help much, as I don't use JSON from Excel, but the site has multiple links and downloadable projects, that include code that seems can be used with Excel, to convert to and from JSON.

    http://ramblings.mcpher.com/Home/exc...on/howcjobject
    Rui
    -------
    R4

  9. The Following User Says Thank You to ruirib For This Useful Post:

    XPDiHard (2015-07-25)

  10. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    The session ID may be one you make up using a UUID.
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=1066

    Are you able to post the JSON code or description?

    cheers, Paul

  11. The Following User Says Thank You to Paul T For This Useful Post:

    XPDiHard (2015-07-25)

  12. #8
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    I fear I cannot help much, as I don't use JSON from Excel
    No prob,

    It was un-forseen change, but expected. The company was once thriving with VBA coders to access the server, then it went to XML, (I still stuck around with my proto-type development on test mode in VBA, just to sort out the logic and do the stats.), now that my initial tests and tweaking of the entire workbook, works, and was hoping some of the company's old stand-alone software was the next step in the process, they jumped onto this JASON.
    Perfectly annoyingly normal, gotten used to constant changes in this part of the IT neck of the woods.
    I'm not the only one who's going, "ohh shhh...,"

    I did read somewhere on those links, they illustrated where Excel/VBA does OK with XML, but not with the JASON language, for now. From what I understand, has to be converted, I think.
    Then I tried to log into their forum to ask specificaly the issue, but first have to become a Google member.

    I don't want to become a Google member, I vote "No", and wait. < grin>

    Thanks.

  13. #9
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Are you able to post the JSON code or description?
    Sent some interim info

    Thanks

  14. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    The JSON coding is basically CSV surrounded by braces so there should be no big issue picking out the data. If you need some help you could post some sample data here.

    cheers, Paul

    p.s. I generally don't respond to private messages, but am happy to write the response here.

  15. The Following User Says Thank You to Paul T For This Useful Post:

    XPDiHard (2015-07-25)

  16. #11
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    OK

    here it is..

    http://media.tatts.com/documentation...gToteBets.html


    The, WP, MR, 5, 4, 10 and 0.0 with passwords etc in the code below would be ready in a Sheet in whatever Cell Ranges to suit.
    In other words, stopping the process to type this data, (WP, MR, 5, 4, 10 and 0.0 with passwords etc) manually won't do.

    "BetType": "WP",
    "MeetingCode": "MR",
    "RaceNumber": 5,
    "Runners": [4]
    "WinInvestment": 10,
    "PlaceInvestment": 0.0


    {
    "CustomerSession": {
    "SessionId": "29012788-71e0-4042-8609-d31a02ea32eb"
    },
    "Bets": [
    {
    "BetType": "WP",
    "MeetingCode": "MR",
    "IsPresale": false,
    "RaceNumber": 5,
    "IsRover": false,
    "Selections": [
    {
    "Field": false,
    "Runners": [4]
    }
    ],
    "WinInvestment": 10,
    "PlaceInvestment": 0.0
    }
    ]
    }

  17. #12
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    You have a single record with arrays and sub-arrays.
    Record = Bets and is an array, square brackets.
    Fields = BetType, MeetingCode etc.
    Selections is a sub-array and Runners a sub-array of that.

    To use the data you need to enumerate each record using some of the code samples linked above.

    cheers, Paul

  18. The Following User Says Thank You to Paul T For This Useful Post:

    XPDiHard (2015-07-25)

  19. #13
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Where is the JSON code written on ?
    In a Excel VBA module ?
    I have provided a workbook to try this and it errors all over the module in reds.

    Here is a complete code provided publicly, but no information on where it goes.
    This I think this is exactly what's required.


    Code:
    Sub Tatts_Login()
    
    Dim sURL As String, sHTML As String
    Dim oHttp As Object
    Dim userName As String
    Dim userPass As String
    Dim Body As String
    Dim sessionID As String 'Add this variable to the General Declarations section for use outside of this subroutine
    
    '‘Change the details below to access your account
    'userName = 'Your user Number / ID / Email here'
    'userPass = “Your Account Password here”
    
    '‘Add a reference to the MSXML type library
    Set oHttp = CreateObject(“MSXML2.XMLHTTP”)
    ‘or
    ‘Set objRequest = New MSXML2.XMLHTTP
    
    '‘The website to login to your account
    sURL = “https://api.tatts.com/sales/vmax/web/account/login”
    
    '‘ The json string to send. It is paramount that the syntax is correct, the result must return: {“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
    Body = “{“”Username””:””” & userName & “””, “”Password””:””” & userPass & “””, “”Dob””:””””, “”DeviceName””:””””, “”DeviceKey””:””””, “”Referrer””:””””}”
    
    oHttp.Open “POST”, sURL, False
    oHttp.setRequestHeader “Content - type”, “application / json”
    oHttp.setRequestHeader “Accept”, “application / json”
    
    '‘Send the login string to the server. Adding parentheses around (Body) forces it to be passed By Value.
    oHttp.send (Body)
    
    '‘Server response.
    sHTML = oHttp.responseText
    
    '‘The sessionID is required for a majority of the calls to the server
    sessionID = Mid(sHTML, InStr(1, sHTML, “SessionId”) + 12, 36) ‘InStr(1, sHTML, “}”) – 35)
    
    '‘Verify that the login was successful.
    If Mid(sHTML, Len(sHTML) – 5, 6) = “:true}” Then
    MsgBox “Logged ON, SessionID: ” & sessionID
    
    Else
    
    Set oHttp = Nothing
    MsgBox "An error occured logging on to the Tatts site. Please re-enter your login details."
    Exit Sub
    End If
    
    Set oHttp = Nothing
    
    End Sub
    Attached Files Attached Files

  20. #14
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    I can't say if this is literally solved, but some help from a referrer got it to work using python as the "go between", between VBA and JSON.

    VBA and JSON, don't "talk" to each other.

    VBA and python do "talk" to each other

    python "talks" to JSON.

    VBA makes txt files from data inside workbook cell references, the data JSON requires.

    python "reads" the txt files made by VBA
    or, sends that data within, to JSON.

    JSON then works.

    However, to get JSON to communicate back is another hill to climb, it has to eventually
    get back to VBA, via python, again ?
    So VBA can log.txt what JSON is doing, done, or if there was an error.

    Conclusion from what I gather and understand,
    It was suggested we now learn python.
    Personally I do not want to learn python, I can hardly do VBA, what I do is rather basic to say the least.

    But if this project with Excel/VBA is to continue, some sort of language other than python needs to be learnt to communicate to JSON via Excel VBA .txt files ?
    If not python, what other is there ?

    Wish list
    Can VBA "talk" directly to JSON ?

    But it worked, the result was positive as in the, functions of all manner of things using python as the go-between


    "dumb" question.

    If then JSON, "new kid on the block" becomes the chosen one for what it supersedes, and what it supersedes currently is on "talking terms" with VBA, what then if VBA and JSON don't communicate ?
    Last edited by XPDiHard; 2015-07-25 at 10:42.

  21. #15
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I don't know exactly what type of environment you are running on, but if you are considering Python, I would probably think using .Net would be a logical alternative. .Net supports multiple languages, including Visual Basic.Net (VB.Net), which shouldn't be too strange to someone who uses VBA. .Net is a rich environment, with a lot of open source code available to consume and communicate JSON. Visual Studio, Microsoft's premier development IDE, uses Json.NET, a free, open source framework.

    I am not sure what you are trying to do and how you are trying to do it. .Net (either natively or through a gazillion of frameworks and toolkits, some free, such as Syncfusion's Enterprise Studio) supports accessing and processing Excel files, in a very similar way to what you do with VBA (it can be actually based on the same DLL's VBA uses). So, it would seem to me that a .Net app, written in any of the support languages (be it VB.NET or any other - my favorite is C#), would be the "obvious" way to accomplish what you need. Not only can you process JSON in .Net, but you can also communicate with any server, sending and receiving JSON while you do it.

    I am not knowledgeable enough about it, but I would even be looking at the possibility of using a framework such as Json.Net from VBA. I am sure it is not that far-fetched.


    P.S.: Yep, a quick search showed a lot of interesting bits, including this:

    How to: Access DLLs in Excel

    It's not the nicest syntax and it's definitely not the easiest way to do it, especially if you are not used to using DLLs and such. if that is the case, maybe it would be worthwhile to explore the .Net possibility. It seems way more natural than getting Python into the mix. Access uses .Net, so ...


    P.S.2: The links I posted earlier seem still valid to me. You can do it from Excel VBA directly:

    http://ramblings.mcpher.com/Home/exc...son-conversion

    Seems easier than learning a new language.

    P.S.3: Answering some of your questions. JSON is simply a text based format, not a language. So, it really doesn't make sense to ask the VBA can talk to JSON and vice-versa. Surely you can generate a text file from VBA and you can read from a text file in VBA. If you can do that, you can write and read JSON. The issue is that, depending on what you want to communicate through JSON, generating JSON manually and back, from VBA, may be a lot more work than it needs to be. That's where libraries come into play.
    Rui
    -------
    R4

  22. The Following User Says Thank You to ruirib For This Useful Post:

    XPDiHard (2015-07-25)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •