VSTO & .NET & Excel

January 13, 2014

VBA Enumeration Database

Filed under: .NET & Excel, Database, Developer sites, Excel, SQL Server, SQLite, Tools, Valentina DB — Dennis M Wallentin @ 3:24 pm

Whenever CodeProject send their newsletter I take the time to read it more carefully. Although not every time but quite often I find one or more gems among the presented articles.

The article VBA Enumeration Database should be read by every seriously MS Excel developer as it covers one of the cornerstones in VBA development; interaction with databases.

So I recommend You to click on the above URL, start to read and then download the code!

Please note, You must be a member of CodeProject to download.

Kind regards,
Dennis

PS:¬†I must confess; I have not written a single line of VBA code for the last 6-8 months. At least, what I can remember ūüėČ

January 30, 2012

Best Practices Export Data to Excel

If You’re a VBA Developer You should know what best practices is to export data to MS Excel. Two expressions summarize it; Microsoft ActiveX Data Object Library and CopyFromRecordset.

However, if You’re a .NET developer and face a situation where You must find a solution to export data to MS Excel then there is a chance that You don’t know the best practices. In general, working on the .NET platform means that we use .NET classes to solve various tasks. When it comes to data acquiring two of the more popular approaches are to use ADO.NET and LINQ. However, as this blog article shows using classic ADO is the fastest way to dump data into a worksheet.

When lurking around various public Q&A forums I have noticed that the question “how to export data to MS Excel” is asked over and over. This article is the answer to that question and it will provide You with the best approach when it comes to speed.

Initially we need to set a reference to “Microsoft ActiveX Data Object x.x Library” where “x.x” refers to the version number in our .NET solution. Next, we must explicit add a reference to the Object Library which is done in the below code. Don’t forget to add the library to the prerequisites or copy the required files to the project.

Imports ADODB
Imports System.Text
Imports excel = Microsoft.Office.Interop.Excel

Public Class frmMain

Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) _
Handles btnExport.Click

Const stcon As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\NorthWind.accdb"

Const stsql As String = "SELECT * FROM Invoices"

Dim cnt As New ADODB.Connection

Dim rst As New ADODB.Recordset

Dim fld As ADODB.Field

'Open the database connection.
cnt.Open(stcon)

'Open the Recordset.
With rst
.CursorLocation = CursorLocationEnum.adUseClient
.Open(stsql, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly, _
ADODB.CommandTypeEnum.adCmdText)
.ActiveConnection = Nothing
End With

'Closing the database connection.
cnt.Close()

'Variables for Excel and the created workbook.
Dim xlapp As New excel.Application
Dim xlwbook As excel.Workbook = xlapp.Workbooks.Add( _
excel.XlWBATemplate.xlWBATWorksheet)
Dim xlwsheet As excel.Worksheet = _
CType(xlwbook.Worksheets(1), excel.Worksheet)

Dim xlrange As excel.Range = xlwsheet.Range("A2")
Dim xlcalc As excel.XlCalculation

'Temporarily turning off the auto calculation.
With xlapp
xlcalc = .Calculation
.Calculation = excel.XlCalculation.xlCalculationManual
End With

Dim ifieldcounter As Integer = Nothing

'Writing the field names to the worksheet.
For Each fld In rst.Fields
xlrange.Offset(0, ifieldcounter).Value = fld.Name
ifieldcounter = ifieldcounter + 1
Next

'Dump the recordset into the worksheet.
xlrange.Offset(1, 0).CopyFromRecordset(rst)

'Closing the recordset.
rst.Close()

With xlapp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlcalc
End With

fld = Nothing
rst = Nothing
cnt = Nothing
xlrange = Nothing
xlwsheet = Nothing
xlwbook = Nothing
xlapp = Nothing
End Sub
End Class

That’s it!

Kind regards,
Dennis

January 13, 2011

Lotus Notes & Excel & VBA

Filed under: Developer sites, Excel, Tools — Dennis M Wallentin @ 11:51 pm

First of all, a belated happy new year to everyone who regular visit my blog. I hope to continue to feed the blog with interesting material also under the year 2011. We are up to some challenges now that corporates tend to roll out Windows 7 and Office in a wider scale.

Anyway, last year I closed my English site and I plan to re-launch it during the year but without the code articles. As per agreement with Microsoft some of them have been published at MSDN. In addition, some people have been in touch with me and also with other developers in the online community about where to find my entries about controlling Lotus Notes via Excel and VBA. Until now they have only been available on my local backup.

I’m very happy to announce that Ron de Bruin has accepted to host these articles at his site. Ron has for the last couple of years built-up a great number of excellent articles on various topics like data acquisition (ADO/DAO), Ribbon UI and interacting with MS Outlook, Outlook Express and Windows Mail from MS Excel. In view of the content of his site it’s great that Lotus Notes has been added to the list of e-mail clients to be covered.

The head page for the Lotus Notes section can be found here. I have agreed to update the examples when necessary and also to add new tips when possible.

Kind regards,
Dennis

March 8, 2010

Information about ExcelKB

Filed under: .NET & Excel, Developer sites, SQL Server, Tools, VSTO & Excel, XL-Dennis' freewares — Dennis M Wallentin @ 7:35 pm

Since 2004 I have been running ExcelKB where I have posted articles about Excel and .NET/VSTO on a regular basis. Overall it has had a quite a number of visitors and also a great number of page views.

Under all these years I have paid for the service of using a Microsoft SQL Server database¬†to handle¬†all the articles. Now I have decided to no longer use that service and therefore all the articles will be dropped. The¬†site itself will only be a “Window” for my business, free tools and¬†together with my¬†upcoming commercial tools.

If You want to save any article from the site than do it as soon as possible as the articles will be dropped by the end of March this year. I may consider posting some of them here later but at this stage I do not want to promise anything.

From now on this blog will be the only place where I will view my opinions and share my knowledge on various topics. In other words, You know where to find me!

Kind regards,
Dennis

Create a free website or blog at WordPress.com.