Run VBA code from text file

Question

I have a code in VBA that queries the database and saves it in a text file, but to run this script I have to open the Excel and perform the macro, this process has to be carried out from minute to minute, as I need the information in real time.

It is possible to run this macro from a text file to avoid unnecessary memory consumption?

Answer 1

Have you tried creating a file vbs (VB Script)? In this file you can use VB Script to do whatever you want, independent of excel. There are probably some differences, but nothing that can’t be adapted.

Examples in Vbscript:

bank create.vbs

' criando banco de dados

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;Data Source=MIGUELANGELO-NB\SQL2008R2; Integrated Security=SSPI;"

On Error Resume Next
    Set command = CreateObject("ADODB.Command")
    command.ActiveConnection = conn

    command.CommandText = "CREATE DATABASE TestDb"
    command.Execute()
    If Err.Number = 0 Then

        command.CommandText = "     " &_
        "   USE TestDb;             " &_
        "   CREATE TABLE Pessoas    " &_
        "   (                       " &_
        "       id int NOT NULL,    " &_
        "       nome varchar(max),  " &_
        "       PRIMARY KEY (id)    " &_
        "   );                      "
        command.Execute()

        If Err.Number = 0 Then

            command.CommandText = "USE TestDb; INSERT INTO Pessoas (id,nome) VALUES (0, 'Miguel');"
            command.Execute()

            command.CommandText = "USE TestDb; INSERT INTO Pessoas (id,nome) VALUES (1, 'Angelo');"
            command.Execute()

            command.CommandText = "USE TestDb; INSERT INTO Pessoas (id,nome) VALUES (2, 'Santos');"
            command.Execute()

            command.CommandText = "USE TestDb; INSERT INTO Pessoas (id,nome) VALUES (3, 'Bicudo');"
            command.Execute()

        End If
    End If
On Error Goto 0
conn.Close()

read-bank.vbs

' lendo o banco de dados

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;Data Source=MIGUELANGELO-NB\SQL2008R2; Initial Catalog=TestDb; Integrated Security=SSPI;"

Set rs = CreateObject("ADODB.Recordset")

Set fso = CreateObject("Scripting.FileSystemObject")
const ForWriting = 2
Set file = fso.OpenTextFile("saida.txt", ForWriting, True)

rs.Open "SELECT * FROM Pessoas;", conn
While Not rs.EOF
    id = rs("id")
    nome = rs("nome")
    file.WriteLine "id = " & id & "; nome = " & nome
    rs.MoveNext()
Wend

rs.Close()
conn.Close()
file.Close()

It is also possible to use Jscript (Javascript) instead of Vbscript.

To run just double click on the file that should already work. Unless you have associated .vbs with another program. In this case, you have to run using command line: cscript nome-arquivo.vbs

Answer 2

You can Create a Batch File to Call Your Excel File.

call C:\diretorio\do\arquivo\ArquivoExcel.xlsm

And in your Excel file you leave your macro as auto executable when opening the spreadsheet.

Ex.: if every time the excel file is opened you want it to show a message "Welcome!"

1 - In VBA you select "This workbook"

2 - In the tab just above, in place of General, already select "Workbook"

vba will give you the command line "Private sub workbook_open()" and then you paste your macro in.

Private sub workbook_open()
Msgbox "Bem vindo!"
End sub

Ready. You can close the spreadsheet and run the file .bat it will open the spreadsheet and the macro will be executed automatically.

Observing: To get even better, you can download a converter .bat for .exe and turn it into an app. Just for more presentable (Maybe Voce wants other users to run it).

Answer 3

The solution is to use VBS instead Excel VBA, as said by @Miguel Angelo.

Below follow more details (I was complementing his reply but it ended up getting too long).

First program in VBS - create a file called test.vbs with the following content:

msgbox "Hello, World!"

When you double-click the file, this message will be shown on the screen. Simple as that. The script can also, of course, be invoked by the command prompt (aka "DOS").

Excel uses VBA (Visual Basic for Applications) and this other method uses VBS (Visual Basic Script). There are differences (for example, in VBS you do not specify the type of variables) but it is very easy to convert one into the other. And using VBS you can also access Excel resources if you need.

Example of how to access the database using ADO in VBS: https://stackoverflow.com/questions/8429313/a-generic-vbscript-function-to-connect-to-any-database-and-execute-any-sql-query

Example of how to read text file in VBS: https://stackoverflow.com/questions/3117121/reading-and-writing-value-from-a-textfile-by-using-vbscript-code

Between these examples and the code you already have in the Excel macro, the differences are basically the statements of variables and the way to get Activex objects. For example, in VBA in Excel you do so:

Set conexao = New ADODB.Connection

And using VBS you do so:

Set conexao = CreateObject("ADODB.Connection")

To run the script at regular intervals, you can use the Task Scheduler or you can loop your own script node with a wait time between iterations. For example:

WScript.Sleep 1000 * 60

The above code keeps the script execution stopped on this line for a minute.