Automating a Data Paging using VBA on Excel
Introduction
Critical situations, especially for small business with hardware and/or software limited. Also on places with Internet access limited, we have need to reduce the network traffic content to query information from a database.
An excellent alternative (if this information not require security), is to keep the data on the user's machine through an easy and familiar user interface (UI) to help manipulate data. This product is MS Excel.
Let's make a query on a SQL Server database and provide data in an Excel spreadsheet, using the paging feature to reduce the data amount that will be displayed to the user.
This article is part of Data Paging series.
Building the Environment for Testing
So we can use the data paging on Excel spreadsheet and, we will see their benefits for all environment where data will be transferred. Let's create a VBA Excel spreadsheet using this "SelectionChange" event on "WorkSheet" object.
Once again we remind you that the T-SQL script to use data paging can be found in another article.
The purpose of this article is show you how to access data and automate the paging process. We don't detail about use of "Visual Basic for Applications" on Integrated Development Environment (VBA IDE) to MS Office.
Environment Requirements
You may run correctly the data paging on MS Excel spreadsheet, following requirements needed:
- Microsoft Excel 2003 or later;
- Confirm that you have user permission on SQL Server database;
- Microsoft Data Access Components (MDAC) 2.1 or later;
Automation in Excel using VBA
It's important to remember to properly simulate data paging into MS Excel as shown in this article, you need to select the component for manipulate data using VBA: the Microsoft ActiveX Data Objects 2.1 Library (ADO) or later.
You need to select the "Tools" menu and then click "References ...".
Image 1 - See below the "References" menu to select the "ADO" on VBA project
Once you click "References ..." menu, a new window appears with Win32 DLLs (not .NET Framework) list available on your PC, so you can select the component to your VBA project.
Image 2 - See below the "Project References" window (click image to enlarge)
Once loaded, the library will be ready for use into your project, then began to code the behavior of the Excel worksheet on "SelectionChange" event. This way, when the user changes focus of a cell this event will be triggered.
The statement to find objects in a particular worksheet depends on the name of this worksheet on the project and not the name of the tab to view the user. This sample, the worksheet name on VBA Project is "Sheet1" and the display name for users is "Paging".
To load the data into the correct row on Excel Spreadsheet without overwrite the formatted fields for configuration and paging, set the "iRowGrid" variable on VBA Project to define the start row for data loading and also to help indicating the "Loop" for each cell to be populated.
Image 3 - See below the VBA code with references to the Excel Spreadsheet (click image to enlarge)
See the VBA code below
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim iRowGrid As Integer
If Target.Row <= 2 And Target.Column = 2 Then
If Plan1.Cells(Target.Row, Target.Column) = "" Then
MsgBox "Digit a number !"
Plan1.Cells(Target.Row, Target.Column).Select
Exit Sub
End If
Conn.Open "Provider=SQLNCLI11;Data Source=127.0.0.1\PROD;Initial Catalog=WI_INFRA;User Id=YourSQLUser;Password=;"
RS.Open "EXEC PR_PAGING " & CStr(Plan1.Cells(1, 2)) & _
"," & CStr(Plan1.Cells(2, 2)), Conn, adOpenForwardOnly, adLockReadOnly
'Start at "row 5" on Excel Spreadsheet
iRowGrid = 5
Do While Not RS.EOF
Plan1.Cells(iRowGrid, 1) = RS("ID_EXAMPLE")
Plan1.Cells(iRowGrid, 2) = RS("NM_EXAMPLE")
Plan1.Cells(iRowGrid, 3) = CStr(RS("DT_CREATE"))
iRowGrid = iRowGrid + 1
RS.MoveNext
Loop
RS.Close
End If
Set RS = Nothing
Set Conn = Nothing
End Sub
Before you obtain the data and perform paging, we define a basic structure of rows and columns to set paging, named "Page Number" (A1) and your value (A2), beyond the amount of rows to be obtained for each query data page, named "Rows per Page"(B1) and your value (B2). Then we set the "iRowGrid" = 5, so the contents of the Database Table will be populated in "Sheet1" worksheet always starting the row "5".
The VBA code uses the stored procedure "PR_PAGING" to get the data and loads the entire contents into "ADO RecordSet" object that declare as "RS".
The SQL Server instance connection and their database is defined in the "ADO Connection" object, declared as "Conn".
More information about stored procedure "PR_PAGING", see Paging a Query with SQL Server.
Image 4 - See below the Excel spreadsheet referring to page "1" with "10" rows/page
We can see the content change when modified the "Page Number" (B1) value and the "Rows per page" (B2) value.
Image 5 - See below the Excel spreadsheet referring to page "2" with "5" rows/page
The video below is just to facilitate the understanding of how this automation data collection and paging works.
See the video demo with this data paging (double click on the video for "Full Screen")
View
Conclusion
Make it possible for your users to get several read only information, quickly and through a familiar interface as MS Excel, becomes an great advantage to keep a whole team with data updated and offline mode.
This allows your users are not dependent on link to Web or inside your intranet to Database query.
References
See Also
Other Languages
This article was awarded the gold medal in the TechNet Guru of July 2014