Skip to content

How to run a VBA query?#

You can perform API method requests in Visual Basic for Applications (VBA).
Visual Basic for Applications is a simplified implementation of the Visual Basic programming language built into the Microsoft Office line of products (including for Mac OS), as well as many other software packages such as AutoCAD, SolidWorks, CorelDRAW, WordPerfect and ESRI ArcGIS.

How to run a VBA query from Excel#

In order to run a request in Excel, you must enable the Developer tab:

  1. On the File tab, go to Options > Customize Ribbon.
  2. In the Customize Ribbon section, in the Main Tabs list, select the Developer checkbox.

web

Next, open the Developer tab, select the Visual Basic subtab.

web

In the VBA window that opens, go to the Insert > Module tab to create a workspace for writing the query.

web

Insert an example VBA query into the module window. For example, we use sending a message using the SendMessage method.

Sub SendMessage()
    Dim url As String
    Dim RequestBody As String
    Dim http As Object
    Dim response As String

    ' The apiUrl, idInstance and apiTokenInstance values are available in console, double brackets must be removed
    url = "{{apiUrl}}/waInstance{{idInstance}}/sendMessage/{{apiTokenInstance}}"

    ' chatId is the number to send the message to (@c.us for private chats, @g.us for group chats)
    RequestBody = "{""chatId"":""70123456789@c.us"",""message"":""Hello World""}"

    Set http = CreateObject("MSXML2.XMLHTTP")

    With http
        .Open "POST", url, False
        .setRequestHeader "Content-Type", "application/json"
        .Send RequestBody
    End With

    response = http.responseText

    Debug.Print response

    ' Outputting the answer to the desired cell
    Range("A1").Value = response

    Set http = Nothing
End Sub

To run a request:
1. Enter the value of apiUrl, idInstance and apiTokenInstance from console, removing the double curly braces;
2. Specify the number of the message recipient in the chatId field and the message body in the message field;
3. Click on web to run the macro;
4. The message will be sent to the specified number, the response body contains the message ID (idMessage) and will be displayed in the specified cell on the sheet.

web