Import Data Into Excel – VBA

A lot of power Excel users like to use VBA to import data because it is very powerful and flexible.  VBA allows us to apply Excel functions and user defined business processes to each records.  After importing records, we can use VBA to analyze data, prepare reports and collaborate the results with other business users.

I am going to show you a very simple VBA example on how to use Active Data Objects (ADO) to import data into Excel.

• Start Excel. Open a new workbook
• Start the Visual Basic Editor and select your VBA project.
• On the Tools menu, click References.
• Click to select the most recent version of the Microsoft ActiveX Data Objects Library check box.
• Cut and paste the following code into the program.
• You may have to change the connection string and the Select statement.

  ‘ Create a connection object.
  Dim cnPubs As ADODB.Connection
  Set cnPubs = New ADODB.Connection

  ‘ Provide the connection string.
  Dim strConn As String

  ‘Use the SQL Server OLE DB Provider.
  strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=(local);"
  strConn = strConn & " Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=VOSTRO;"
  strConn = strConn & " Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=AdventureWorks"

  ‘Now open the connection.
  cnPubs.Open strConn

  ‘ Create a recordset object.
  Dim rsPubs As ADODB.Recordset
  Set rsPubs = New ADODB.Recordset

  With rsPubs
      ‘ Assign the Connection object.
      .ActiveConnection = cnPubs
      ‘ Extract the required records.
      .Open "SELECT * FROM [AdventureWorks].[HumanResources].[vEmployee]"
      ‘ Copy the records into cell A1 on Sheet1.
      Sheet1.Range("A1").CopyFromRecordset rsPubs
     
      ‘ Tidy up
      .Close
  End With

  cnPubs.Close
  Set rsPubs = Nothing
  Set cnPubs = Nothing

  Conclusion

  In real life, I don’t think any Excel VBA programmer would develop such simple function; otherwise, they may want to use data connections instead.  I just want to use this example to show you some of the key components of retrieving data using VBA, e.g. ADO, Connection String, and Recordset.

• Import Data Into Excel – XML
• Import Data Into Excel – VBA
• Import Data Into Excel – Data Connection
• Import Data Into Excel – SQL Server Import and Export Wizard

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!