Tag: Excel

Posted on

Exporting Data from Lotus Notes Databases

Exporting from Lotus Notes

Lotus Notes was innovative and, by most accounts, a great collaboration software in its heyday. Though there are still some 35 million users of Notes, there are regular rumors of its imminent demise.

Because of this, some organizations that have used Notes over the years may want to move to some other system, such as SharePoint. Getting data out of Notes for reporting or for use in a different system is not very straightforward, as there are several options depending on your needs.

The first is to connect Notes to SQL, which I covered back in June 2012.

Exporting from Lotus Notes
Exporting from Lotus Notes

Another is to export the data into Lotus 1-2-3, and then into Microsoft Excel, if need be. This is problematic for many Excel users today, as you cannot open Lotus 1-2-3 files with versions of Excel newer than Excel 2003.

Exporting view data into a comma separated value (.csv) file is another option. I have read that this particular option does not always work exactly right, especially if there are commas or some other special characters in the data, so YMMV.

The main problem with all of the above options is that you can only export data shown in a particular view with these methods. Unless you want to create (or already have) a view that has all document fields in the view, you won’t be able to see them all. If you want to export all the data in each document, you must export documents into what is called “Structured Text”.

Structured Text, while readable by text editors, cannot be directly imported into CSV or other formats using only Word or Excel. However, some tools been developed for this purpose. One example of such a tool is the Structured Text Parser (STP) written by James J. Schwaller. Tools such as these will make converting data from Lotus Notes into other formats or for use in other systems much simpler, and will not require hiring contractors or buying expensive tools that you will not need once your data is converted.

Good luck!

Posted on

Exporting an HTML Table to Excel While Applying Relative Cell References Using ColdFusion Markup Language

Adobe ColdFusion logo

A new problem I was tasked with was to make enhancements to a report built using CFML by changing the totals column to a SUM() function when the report is exported to Microsoft Excel.

The trick here was that the table was being exported as HTML code, and then opened in Excel. Since the location of the totals row would vary depending on the number of rows in the report, it was uncertain as to how the SUM() function would be constructed. Regular cell references (such as A1:A10, $A1:$A10, etc.) would not be sufficient. The key was to use a combination of the Excel functions INDIRECT(), ADDRESS(), ROW(), and COLUMN().

After much experimentation, I discovered that the magic expression that will give sum all of the rows in a given column and place that value in the cell beneath those rows is:

=SUM(INDIRECT(ADDRESS(1,COLUMN())  ":"  ADDRESS(ROW()-1,COLUMN())))

Finding this was a huge step in being able to complete this task. However, this report had multiple sections and I didn’t want every total to SUM all of the rows in previous sections. Fortunately, I knew how to get the number of rows in each query run by ColdFusion against the database using the RecordCount variable that is a result of the cfquery function.

By changing the CFML code to include the Excel expression in this format:

=SUM(INDIRECT(ADDRESS(ROW()-#whatever.RecordCount#,COLUMN())  ":"  ADDRESS(ROW()-1,COLUMN())))

the resulting spreadsheet replaced the ColdFusion-calculated totals with the Excel expression, and the Excel-calculated totals.

<cfif variables.Export EQ 'True' AND Isdefined("variables.ExportToExcel")>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td colspan="9" style="visibility:hidden;display:none;">&nbsp;</td>
    <td class="num" style="visibility:hidden;display:none;">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
<cfelse>
    <td class="num">#Numberformat(variables.TotalYear,'999,999,999,999')#</td>
    <td class="num">#Numberformat(variables.TotalOMPI,'999,999,999,999')#</td>
    <td class="num">#Numberformat(variables.TotalNBC,'.99')#</td>
    <td class="num">#Numberformat(variables.TotalNetBen,'999,999,999,999.9')#</td>
    <td colspan="9" style="visibility:hidden;display:none;">&nbsp;</td>
    <td class="num" style="visibility:hidden;display:none;">#Numberformat(variables.TotalInv,'999,999,999,999')#</td>
</cfif>
Posted on

Using Excel VBA to Copy an ADODB Recordset to Multiple Worksheets

Microsoft VBA logo

In adding functionality to an ancient Excel VBA reporting app for an Oracle database, I decided to set up a temporary worksheet to use as a place to store data before copying it to multiple other worksheets rather than copying directly from the recordset multiple times.

Basically, this VBA procedure takes an ADODB recordset and a Boolean value as parameters. The recordset is the data source, and the Boolean indicates whether or not the data will be copied to a particular worksheet. The temporary worksheet is cleared before the recordset is copied into it, and is cleared again after the data is copied to the other worksheet(s).

Public Sub CopyToSheets(ByRef rs As ADODB.Recordset, ByVal bCopyToSecondSht As Boolean)
    Dim MainSht As Worksheet, TempSht As Worksheet, Rng As Range, c As Range, lastRow As Long, intNextRow As Integer

    Set MainSht = ActiveWorkbook.Worksheets(cMainSht)
    Set TempSht = ActiveWorkbook.Worksheets(cTempData)

    TempSht.Cells.Clear

    rs.Open
    TempSht.Cells(1, 1).CopyFromRecordset rs
    rs.Close

    lastRow = TempSht.Range("A" & Rows.count).End(xlUp).Row
    Set Rng = TempSht.Range("A1:A" & lastRow)
    For Each c In Rng
        intNextRow = NextRowNumber(MainSht)
        c.EntireRow.Copy MainSht.Cells(intNextRow, 1)
        If bCopyToSecondSht Then
            Dim SecondSht As Worksheet
            Set SecondSht = ActiveWorkbook.Worksheets(cSecondSht)
            intNextRow = NextRowNumber(SecondSht)
            c.EntireRow.Copy SecondSht.Cells(intNextRow, 1)
        End If
    Next c
    TempSht.Cells.Clear
End Sub