Run VBA code from text file

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

Examples in VBScript:

create-banco.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()

ler-banco.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()

You can also use JScript (JavaScript) instead of VBScript.

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

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 you open the worksheet.

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

1 - In VBA you select "This workbook"

2 - On the tab above above, in the General place, 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 worksheet and run the .bat file it will open the worksheet and the macro will run automatically.

  

Note: To get even better, you can download a .bat in> and turns it into an application. Just to stay more   presentable (Maybe you want other users to run it).

The solution is to use VBS rather than Excel VBA, as stated by @Miguel Angelo.

Below are more details (I was completing his response 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 displayed on the screen. That simple. The script can also of course be invoked by the command prompt ("DOS").

Excel uses 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 features if you need to.

Example of how to access the database using ADO in VBS: link

Example of how to read text file in VBS:

Between these examples and the code you already have in the Excel macro, the differences are basically the variable declarations and the way to get ActiveX objects. For example, in VBA in Excel you do this:

Set conexao = New ADODB.Connection

And using VBS you do this:

Set conexao = CreateObject("ADODB.Connection")

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

WScript.Sleep 1000 * 60

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