Import Data Into Excel

There are many instances we need to import data into Excel. The most commonly known method is to use export the data from the database in CSV or text form and then open the exported file in Excel. This method will put the data into an Excel worksheet. The data will remain unchanged.

You can skip a step from the above method by using the data import function provided by Excel. To activate this method, go to the menu and select Data -> Import External Data -> Import Data. This approach can be used for a number of databases, the most common is Access databases, DBase, Excel files, CSV files, Text Files, etc.

The above 2 methods are definitely useful methods for presenting data in Excel. The disadvantage of these 2 methods is that the data imported does not change. The data records remain the same when you opened it the next time, in one day, one week or even one year later. Therefore, to get the updated records from the database, you have to import the data again.

If you need to get the updated records very frequently, such as every day, or every week, then the above 2 methods to import may not be efficient for you. You would have to use the more advanced approach of importing the data, that is, using the MSQuery function provided in MS Office. The function will allow you to import data recently updated from the database when you click on the refresh function given in MSQuery. Alternatively, by set the option to have the data refreshed at the regulary interval. Do note that this option works when the file is opened. To get the updated data all the time, you can turn on the setting to have the query refreshed when you open the Excel workbook. This will make sure that the latest set of data is presented when you opened the workbook. And MS Query allows you to combine more than one table so that data from different data table can be presented in one single worksheet. This approach requires you to install MSQuery that does not come installed in the standard installation (up to MS Office XP) even though it is provided free with the MS Office Package.

The final method to import data that I would like to share with you here is to use VBA or Macro. It is not using macro recording. It is using ADO Connection which offers one of the fastest ways to connect and present data from databases. To use ADO connection, you need to provide a connection to the database (in this case, we are using an Excel workbook as a database. This is done by using the following procedure or set of codes:

Public Sub ConnOpen(ByVal sPath As String)

If Conn.State = 1 Then Conn.Close
'534 (Microsoft Excel 3.0)
'278 (Microsoft Excel 4.0)
'22 (Microsoft Excel 5.0/7.0)
'790 (Microsoft Excel 97)
Select Case Val(Application.Version)
Case Is >= 9  
'use this driver if Excel version is 200 and above
Conn = "Driver={Microsoft Excel Driver (*.xls)};" & _
            "DriverId=790;DBQ=" & _
            sPath & "; " & _
            "DefaultDir=" & ThisWorkbook.Path & _
            ";ReadOnly=False"
            
Case Else
Conn = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & _
            sPath & "\" & _
            ";ReadOnly=False"
                        
End Select

Conn.Open
End Sub

Notice the content in the bracket [Public Sub ConnOpen(ByVal sPath As String)]. It is to allow us to pass values into the sub procedure. In this case, we are to pass the path of the worksheet into the connection.
Next, we need to get the records out from the database. By using the asterisk (*), we can pull out all the records in the data table:

Public Sub RSRun(ByVal SqlStr As String)
On Error GoTo Er
    Set RS = Nothing
    RS.Open SqlStr, Conn, adOpenStatic, adLockOptimistic
'MsgBox SqlStr
Exit Sub
Er:
    MsgBox Err.Description, vbCritical, _
	"Error # " & Err.Number

End Sub

After putting the above 2 sets of codes into a module, you are ready to import data into an Excel worksheet. Let's use a procedure name called "Test" to present the data in worksheet "Sheet1".

Sub Test()

lngCount = 1
ThisWorkbook.Sheets("Sheet1").Cells.ClearContents

' Open the connection.
    Call ConnOpen(ThisWorkbook.Path & "\INV_DataTable")
    Call RSRun("SELECT * " & _
    "FROM `" & ThisWorkbook.Path & _
	"\INV_DataTable`.`OINV$` T0 ")

'The following section is to present 
'the fieldnames in the first row.	
For Each fldname In RS.Fields
     ThisWorkbook.Sheets("Sheet1"). _
	 Cells(1, lngCount) = fldname.Name
     lngCount = lngCount + 1
Next

'This section is to present the data we have retrieved 
'from the database from Cell A2.
ThisWorkbook.Sheets("Sheet1"). _
Range("A2").CopyFromRecordset RS

'You need to close the recordset and connection.
RS.Close
Conn.Close

End Sub

Now, all you have to do is to run the macro called Test and you will be able to import data into the Excel worksheet.