Search code examples
sqlexcelvbarpa

How to get SQL query results easy

This is my first post here so please be forgiving :)

Just at the beginning, I'm not fluent in any programming language, but I mostly use SQL, very rarely VBA. I'm mainly RPA Developer with Kofax Kapow RPA software.

So I have a SQL query that I need to run to get some results that gonna be later used in RPA bot. There is a built in functionality to run SQL within RPA software but this functionality is very poor and doesn't allow me to use many functions that are embedded in my SQL query (like regex,listagg, exc.)

So what I would like to do is to run quickly VBA code without opening any SQL sotware. The best option for me would be to create kind of .exe file from for example MS SQL Server oraz Oracle SQL where this exe would result in creating a separate file (xls,XML,csv or any other) with the results

I know that sometimes like this is possible within a Excel VBA macro but I have some problems with excel macros within RPA software, since sometimes there is a yellow bar popping up at the top of the file and you need to "enable content" to run the macro. When I switched it and this bar doesn't appear, the macro didn't work too. This bar and things like this make a RPA bots less stable

So, hopefully I have described the issue here pretty well :)

Do you know how I may make this things (this file) happened ?

Solution

  • Unfortunately you are not very clear in your problem description, but I go from what I can understand.

    So you must run an SQL query and you must first construct this query?

    You can use VBA to construct a query string in which you use VBA to embed the parameters into the string. For example, the parameters are in the Excel sheet, and you create one query string from each row.

    Then you can either print these strings to a text file that you then can use in your SQL environment, or you use ODBC in Excel VBA to connect to your database, run each query, retrieve each query's result and print them or place in an Excel sheet.

    Does any of these possibilities help you?

    Example:

    Function makeQuery(myTable, myCol, myVal) As String
    Dim sSQL As String
    
    sSQL = "SELECT * FROM " & myTable & " WHERE " & myCol & "='" & myVal & "';"
    makeQuery = sSQL

End Function

Sub example()
    Open "myQueries.txt" For Output As #1
    Print #1, makeQuery("table1", "memberName", "Johnson")
    Print #1, makeQuery("table1", "memberName", "Adams")
    Close #1
End Sub