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.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

3 Responses to Import Data Into Excel – VBA

  1. Pingback: Import Data Into Excel – XML « Technologies and your business

  2. Pingback: Import Data Into Excel – SQL Server Import and Export Wizard « Technologies and your business

  3. Pingback: Import Data Into Excel – Data Connection « Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: