VB6: Manage Excel Reports Not By Automation

By | 2017-03-21

Introduction
Ever since I wrote the first VB6 article and MS Excel automation I have had this type of article in my mind. As this article shows, it’s possible to interact with workbooks without having MS Excel installed even with an old tool like classic VB. Here the classic Microsoft ActiveX Data Object (ADO) model is used to retrieve data and to add it to a MS Excel report. It’s still based on the same case, Pivot Report, first presented in B4J: Generate Excel Reports.

The Case
To retrieve data from the SQLite database in use I use classic ADO. One drawback with classic ADO is that when inserting data into a worksheet all values are of the same data type, Text. In order for MS Excel to handle some of the retrieved data we must convert text values to numerical values. This is done in VBA and therefore the Excel template in use is of the type macro-enable workbook.

Because Excel cannot update the Pivot Table and Pivot Chart automatically due to the issue text/numeric values this must also be done in VBA. The report template have been updated with a VBA code module and in the report’s GUI a button has been added.

When targeting Excel’s workbooks and using the SQL expression “INSERT INTO…” it’s necessary to have a named range for the targeting table. The Pivot report’s table has headers (see below) so we give them one name, “DataPivot”.

The following screen shot shows the updated GUI for the template:

The following code is the contest for the added VBA module:


Option Explicit

'Don't forget to add a proper error handler.
Sub Refresh_Report()

Dim wbReport As Workbook
Dim wsReport As Worksheet
Dim rnStartPoint As Range
Dim rnEndPoint As Range
Dim rnData As Range
Dim ptReport As PivotTable

Dim lnLastRow As Long

Application.ScreenUpdating = False

Set wbReport = ThisWorkbook
Set wsReport = wbReport.Worksheets(1)

With wsReport
Set rnStartPoint = .Range("E4")
lnLastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
Set rnEndPoint = .Range("E" & lnLastRow)
Set rnData = .Range(rnStartPoint.Address, rnEndPoint.Address)
End With

'Convert text to integer.
With rnData
.NumberFormat = "#,##0"
.TextToColumns Destination:=rnStartPoint
End With

'Update the Pivot Table (and as well as the Pivot Chart).
Set ptReport = wsReport.PivotTables(1)
ptReport.RefreshTable

MsgBox "All data updated!", vbOKOnly, "Report"

End Sub

The following code is part of the VB6-solution:

Option Explicit

'Reference to Microsoft's ActiveX Data Object Library 6.0
'Reference to Microsoft Scripting Runtime

'Don't forget to add a properly error handler.

Const cTitle As String = "Excel Report"

Private Sub btnAdd_Data_Click()

If GrabData = True Then
MsgBox "The workbook successfully updated!", vbOKOnly, cTitle
Else
MsgBox "Sorry, we are having a bad day...", vbCritical, cTitle
End If

End Sub
Private Function GrabData() As Boolean
Dim adoCnt As ADODB.Connection
Dim adoRst As ADODB.Recordset
Dim adoxlCnt As ADODB.Connection

Dim stCon As String
Dim stxlCon As String

Dim stSql As String
Dim stxlSQL As String

Dim stDept As String
Dim stMonth As String
Dim stAmount As String

Dim stPath As String
Dim stDate As String
Dim stTime As String
Dim stFileName As String

Dim FSO As New FileSystemObject

'Input to time stamp report file.
stDate = Replace(Date, "-", "")
stTime = Replace(Time, ":", "")
stFileName = "PivotReport" + stDate + stTime + ".xlsm"

stPath = App.Path

'Copy, move and rename the file.
FSO.CopyFile stPath + "\res\PivotReport.xlsm", stPath + "\reports\" + stFileName

stCon = "Provider=MSDASQL.1;Driver={SQLite3 ODBC Driver};" + _
"Database=" + stPath + "\res\Production.db;"

stSql = "SELECT Dept, Month, Result FROM Prod_Output ORDER BY Dept;"

Set adoCnt = New ADODB.Connection
Set adoRst = New ADODB.Recordset

adoCnt.Open stCon
With adoRst
.CursorLocation = adUseClient
.Open stSql, adoCnt
End With

Set adoxlCnt = New ADODB.Connection

'Notable: since we use a macro-enable workbook we must add 'Macro'
'to the connection string.
stxlCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & stPath + _
"\reports\" + stFileName + ";Extended Properties=Excel 12.0 Macro"

adoxlCnt.Open stxlCon

Do Until adoRst.EOF
With adoRst
stDept = .Fields("Dept").Value
stMonth = .Fields("Month").Value
stAmount = .Fields("Result").Value
End With

stxlSQL = "INSERT INTO [DataPivot] ([Dept], [Month], [Amount]) " + _
"VALUES ('" + stDept + "', " + _
"'" + stMonth + "', " + _
"" + stAmount + ");"

adoxlCnt.Execute stxlSQL
adoRst.MoveNext
Loop

adoxlCnt.Close
adoRst.Close
adoCnt.Close

Set FSO = Nothing
Set adoxlCnt = Nothing
Set adoRst = Nothing
Set adoCnt = Nothing

GrabData = True

End Function

After running the GrabData function the report looks like the following:

Finally, after running the Refresh_Report in the VBA module the final version of the report looks like the following screen shot:

The work done!

Using classic ADO comes also with a benefit, if we add data on several occasions to a table we don’t need to know the first empty row in the table every time. It’s been taken care of by classic ADO.

Since we can use classic ADO as well as ADO.NET in VB.NET the solution can easily be ported to VB.NET. Although it’s doable with the above approach there exist some aspects that should be considered carefully:

  • The risk that classic ADO is not available on computers
  • The shortcomings of classic ADO
  • The use of macro-enabled workbooks and VBA that may not be allowed to be distributed due to security risks.

In upcoming blog entries I will discuss more professional approaches to interact with MS Excel without automation when using VB6. I will also continue to discuss various solutions for scenarios where we use VB.NET.

Enjoy!

All the best,

Dennis

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.