Excel VBATutorial            

             

Excel VBA Macros

Email to excel-vba.com

Excel Tutorial on Macros

 Excel Consulting

Here is a sample of what you will find
in the downloadable 
Tutorial on Excel macros

VBA Code and  SQL

SQL stands for Structured Query Language and is the language used to extract data from almost all databases like Access and SQL Server from Microsoft or, Oracle, Sybase, SAP and also most accounting applications. You can also extract data from the Internet, from text files and from other Excel or CSV files.

Basically you need a connection (varConn in the macro below) and an SQL sentence (varSQL in the macro below) to automate the extraction of data for reporting purposes. In the example below an SQL query extracts all the data from a small Acces database.

Click here to download the small Access database and test the following code from a workbook sitting in the same folder.

Sub proSQLQueryBasic()
Dim varConn As String
Dim varSQL As String

    Range("A1").CurrentRegion.ClearContents

    varConn = "ODBC;DBQ=test.mdb;Driver={Driver do Microsoft Access (*.mdb)}"

    varSQL = "SELECT tbDataSumproduct.Month, tbDataSumproduct.Product, tbDataSumproduct.City FROM     tbDataSumproduct"

         With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
             .CommandText = varSQL
             .Name = "Query-39008"
             .Refresh BackgroundQuery:=False
         End With

End Sub

Open the Excel files vba-sql1 and vba-sql2 for a complete explanation of the code and much more on queries. These two Excel workbooks are part of the Tutorial on Excel Macros.


We hope you have enjoyed this tip
For more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros


To organize your discovery of Excel macros, the downloadable Tutorial on Excel Macros is divided in three sections (all 3  sections part of the single download):

Section 1: Excel Macros Programming (Chapters 1 to 10)
This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. You will also learn about security and discover "events" (an event is what starts the macro).

Section 2: Excel VBA Vocabulary (Chapters 11 to 23)
Developing a macro is communicating with Excel and to do so you need to use a language called Visual Basic for Applications (VBA). In section 2 you will learn all the VBA vocabulary that is essential to work with business data (accounting, sales, production and others).

Section 3: Forms and Controls in VBA for Exce (Chapters 24 to 33)
The userform is a small or large dialog window that you create and allows the user to submit values that will be used by your macros. To these userforms you will add controls (command buttons, text boxes, list boxes and others) and program them.