VBA Code- Extracting Database data into excel sheet using macro

Code to read the data from database and writing data in excel file using VB Macro and VBA.

'''' Naming the sub as auto_open will trigger the sub automatically on opening the file.

'''' We can create a sub with different name and assign macro to a control in the excel

''''file 

Sub auto_open()
'''' define the connection string to connect to the database, it can be a dsn created,

'''' or connection string to create to the database. Please refer connectionstrings.com
'''' to connect to different database using connection string.
connStr = "dsn=testthedb''
'''' Query to fetch data from the database
strGetEmployeeDetails = "select * from employee order by UserName asc;"
''''Provide reference of sheet to add data to in the current workbook. We can add a

''''sheet in excel, or refer to an external excel file/Sheet using excel.application.

''''In this example, Sheet Employee_Details exist in the current excel file
Set worksht = ThisWorkbook.Sheets("Employee_Details")
''''--------------------------
'''' Create database connection
   Set Conn = CreateObject("ADODB.Connection")
   Set rset = CreateObject("ADODB.Recordset")
   Conn.Open connStr
   rset.Open strGetEmployeeDetails, Conn, adOpenStatic

''''The recordset is stored in rset.
''''The first row of data is the column details of the query results

''''and is added in the results header

For i = 0 To rset.Fields.Count - 1
worksht.Cells(2, i + 2) = rset.Fields(i).Name
Next i

''''Copy the recorset data into excel file starting at row 3 and column 2
worksht.Cells(3, 2).CopyFromRecordset rset
rset.Close
Set rset = Nothing
ThisWorkbook.Save
Conn.Close
Set Conn = Nothing
End Sub

VBA Code: How to auto trigger operation on opening or closing an excel file

Problem : We need to perform or auto-trigger some operation while opening or closing an excel file. There may be operations required by excel user like connecting to database and auto populating the data from database  on opening the excel file and generating pivot tables and charts automatically based on database data. Similarly it can be any other event that user want to perform on opening excel file(saved in macro-enabled xlsm file format)

How to create a module in excel VBA?

Open the VBA editor by clicking Alt + F11 or go to Developer tab and click on Visual basic. Below image shows how to create a sub or function in VBA. Following are the steps for creating a sub or function in VBA:

1. Click on Developer

2. Click on Visual basic icon. (The above two steps can be achieved by clicking on Alt + F11.

3. In the workbook, add new module.

4. In the module, add sub as shown below.

How to create a sub that will be auto-triggered on opening the excel file?

Create a sub with name as auto_open as shown in the code below. Try writing a small code as shown below which will display a message box when the excel file opens.

 Sub auto_open()  
   MsgBox "Show message while opening the excel file"  
 End Sub  

How to create a sub that will be auto-triggered on closing the excel file?

Create a sub with name as auto_close as shown in the code below. Try writing a small code as shown below which will display a message box when the excel file closes.

 Sub auto_close()  
   MsgBox "Show message while closing the excel file"  
 End Sub  

Save the excel file in .xlsm format.