Can't figure out the right way to break a long parameter to multiple lines

Question

I've created a script to fetch json response from a website. To get the response I had to issue post http requests along with appropriate parameters. The script is doing fine.

The payload that I've used within the script is substantially long. It could have been longer.

Now, my question is, how can I break such long line to multiple lines?

This is how I've tried:

Sub GetJsonResponse()
    Const URL = "https://api.pcexpress.ca/product-facade/v3/products/category/listing"
    Dim payload$

    payload = "{""pagination"":{""from"":2,""size"":48},""banner"":""loblaw"",""cartId"":""702da51e-a7ab-4f54-be5e-5bf38bd6d7a2"",""lang"":""en"",""date"":""09062021"",""storeId"":""1032"",""pcId"":null,""pickupType"":""STORE"",""enableSeldonIntegration"":true,""features"":[""loyaltyServiceIntegration"",""sunnyValeServiceIntegration""],""inventoryInfoRequired"":true,""sort"":{""topSeller"":""desc""},""categoryId"":""27985""}"
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", URL, False
        .setRequestHeader "content-type", "application/json;charset=UTF-8"
        .setRequestHeader "x-apikey", "1im1hL52q9xvta16GlSdYDsTsG0dmyhF"
        .send (payload)
        Debug.Print .responseText
    End With
End Sub

You can paste your JSON into a worksheet cell (nicely formatted) and read it from there. — Tim Williams, Jun 11 '21 at 19:15

QHarr · Accepted Answer · 2021-06-11T18:46:56.683

Use the & concatenation to join smaller parts. I would personally examine the json structure and then decide on logical breaks (within reason), then transfer to a text editor and use regex/ find and replace to generate the new strings to concatenate based on your chosen line breaks.

Below you will see most lines have payload = payload & " at the start and " at the end, after the break indicated by the ,.

Of course, also replacing inner " with "".

Option Explicit

Sub GetJsonResponse()
    Const URL = "https://api.pcexpress.ca/product-facade/v3/products/category/listing"
    Dim payload$

    payload = "{""pagination"": {""from"": 2,""size"": 48},"
    payload = payload & """banner"": ""loblaw"","
    payload = payload & """cartId"": ""702da51e-a7ab-4f54-be5e-5bf38bd6d7a2"","
    payload = payload & """lang"": ""en"","
    payload = payload & """date"": ""09062021"","
    payload = payload & """storeId"": ""1032"","
    payload = payload & """pcId"": null,"
    payload = payload & """pickupType"": ""STORE"","
    payload = payload & """enableSeldonIntegration"": true,"
    payload = payload & """features"": [""loyaltyServiceIntegration"", ""sunnyValeServiceIntegration""],"
    payload = payload & """inventoryInfoRequired"": true,"
    payload = payload & """sort"": {""topSeller"": ""desc""},"
    payload = payload & """categoryId"": ""27985""}"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", URL, False
        .setRequestHeader "content-type", "application/json;charset=UTF-8"
        .setRequestHeader "x-apikey", "1im1hL52q9xvta16GlSdYDsTsG0dmyhF"
        .send payload
        Debug.Print .responseText
    End With
End Sub

This fits with how I re-arranged this:

To this:

As you noted in the comments, you can absolutely split the string into pieces and continue the line with the line continuation character _.

It seems I can achieve the same like ***[this](https://pastebin.com/kKRWrH4i)*** as well. — SMTH, Jun 11 '21 at 18:36
Absolutely. Trick is to find what works for you. And things you can do easily with regex in a text editor are usually a good idea if dealing with a large string. — QHarr, Jun 11 '21 at 18:44

Tim Williams · Answer 2 · 2021-06-12T04:37:04.353

Using the Windows Clipboard API functions from here: https://learn.microsoft.com/en-us/office/vba/access/concepts/windows-api/send-information-to-the-clipboard

You can copy text to the clipboard and do something like:

Sub ClipboardTextToVbaString()
    Dim s As String, arr, e, rv As String, i As Long, n As Long
    s = GetClipboard()  'read text from clipboard
    If Len(s) = 0 Then Exit Sub
    arr = Split(s, vbCrLf)
    rv = "s = "
    For i = LBound(arr) To UBound(arr)
        e = Replace(arr(i), """", """""")
        rv = rv & """" & e & """ "
        If i < UBound(arr) Then
            If n < 20 Then
                rv = rv & " & vbCRLf &  _" & vbCrLf
                n = n + 1
            Else
                rv = rv & " & vbCRLf" & vbCrLf & "s = s & "
                n = 0
            End If
        End If
    Next i
    'Debug.Print rv
    SetClipboard rv  'set the modified text back into the clipboard for pasting
End Sub

Not very thoroughly-tested but you get the idea: something for your personal.xlsb file... Note this is more aimed at formatting multi-line text into a VB-compatible format - not really for breaking up long single lines, which I guess was your original form.

I like the practicality of this. Clearly I have not deal with sending long enough strings in the past. + Are these declarations artefacts (sep, multi)? Guessing sep is for other uses cases to specify delim? multi a flag for splitting on more than one delim? — QHarr, Jun 12 '21 at 01:46

Can't figure out the right way to break a long parameter to multiple lines

2 Answers2