VSTO & .NET & Excel

August 13, 2014

Articles about installing softwares from SamLogic

I’m aware of the fact that I nowadays rarely make any posts here at all. One explanation is that I’m learning about new tools and that I find it very satisfaction and enjoyable.

Nevertheless, SamLogic, the Swedish vendor of Visual Installer and other excellent tools, have recently published some interesting articles which explain some important aspects when it comes to install softwares:

 

Happy coding everyone!

Dennis

Advertisements

June 8, 2013

Xojo: An Old New Kid on The Block

Until 4th June this year the company had the name Real Software Inc and their product, a cross-platform developing kit, had the name Real Studio. However, the company name is now Xojo Inc and the product’s name is Xojo.

Bob Keeney, founder of BKeeney Software, has written a great review about Xojo 2013r1. I refer to that articles then write one myself.

Here I will bring up two important subjects, Xojo in Windows and Missing Controls – Expensive to buy.

Xojo in Windows

When I started to hang around and lurk around I got the impression that the major group of developer are on the Apple platform. Since then it has been confirmed in many ways and with the transformation of Real Studio UI to Xojo UI it’s quite clear that Xojo Inc favor the Apple platform. Have a look on the below screen shot, is it Windows or?

Xojo in Windows

Missing Controls – Expensive to buy

If I buy a development tool like Xojo I expect to have included a bunch of basic controls. Xojo Inc do not follow the main stream of development tools. They have excluded two important controls, a Data Grid and a Chart control.

In fact, they have placed themselves as hostage to 3rd party controls by some vendors. For developers it gets more expensive and by using the 3rd party controls the developers are also in the hand of the 3rd party vendors.

OK, we start first with buying a license of Xojo Desktop for US$ 300 (excluding VAT).

Suppose we need a descent Data Grid control we then must buy a collection of plug-ins. The collection has a price of US$ 199 (excluding VAT) with a subscription of one year.

Next, we realize we need a chart control. It exist a free chart control but it has not been updated for the last years. Beside that, it lack a lot of feature. Our real option is to buy a commercial Chart control. It cost US$ 26o (excluding VAT).

Let us summarize it:

  • We pay US$ 300 for one Xojo Desktop License
  • We pay US$ 560 for two basic controls!
  • All in all, we pay US$ 860 (excluding VAT)

We actually pay more for two basic controls then what one license of Xojo Desktop cost! That sucks.

Badwill can be created in many ways. Badwill created this way can easily be avoided; provide a Data Grid control and a Chart control as part of Xojo packages.

Is it only me who find it unacceptable?

Xojo is an interesting development too and I find the job done by Xojo Inc to be good. However, given the present feedback Xojo need one or two releases before all the teething are gone.

Finally, the subject for this article is related to the fact that Xojo is based on Real Studio so in one way it’s new in another way it’s old.

Kind regards,
Dennis

March 18, 2013

Great News From Paradigma Software & Syncfusion Inc

Paradigma Software released version 5.0 of its Valentina Database and tools. The hard databases competition is an advantage for us developers; we get better tools and more for our money.

Paradigma Software continue to offer some great tools for free;

  • An updated and more powerful Valentina Studio which now also supports MySQL DB, PostgreSQL DB. And since prevously version Valentina Dbs and SQLite Dbs
  • An updated and more powerful Valentina Server / 5

Of course, Paradigma Software also offers Premium products such as  Valentina Server , Valentina Studio Pro and a great number of ADKs and Report ADKs  for nearly all existing developing platforms.

For more information please visit:

Valentina DB5

Syncfusion, Inc, have also released new versions of their first class components for the .NET platform, WinRT and Windows Phone (Beta). Check it out at:

syncfusion_logo_slogan_300px

Kind regards,
Dennis

PS: I have no commercial interest in any business. I’m just a pleased customer who believes that high quality tools and components should be expoxed to other developers.

March 4, 2013

Office Developer Tools for Visual Studio 2012

Filed under: .NET & Excel, Database, Excel, SharePoint, Valentina DB, Valentina Office Server, VSTO & Excel, XLLs — Dennis M Wallentin @ 11:29 pm

Great news! Microsoft have announced today that the RTM version of Office Developer Tools for Visual Studio 2012 has been launched.

For more information please read the following entry at Somasegar’s blog:

Now Available: Office Developer Tools for Visual Studio 2012

Enjoy!

Kind regards,
Dennis

 

February 22, 2013

POEditor: An Excellent Translation Tool!

It’s very exceptionable that I get thrilled over a software. The last time must be the first version of Excel 2.1d, that’s nearly 30 ago, I got in my hand!

I can only say that it’s an excellent software and it’s also free. It’s so good that I say its speak for itself.

The URL to POEditor is: http://poeditor.com/

Enjoy!

Kind regards,
Dennis

February 4, 2012

The Great Microsoft Office Portal

Filed under: .NET & Excel, .NET Books, Excel, Valentina DB, Valentina Office Server, VSTO & Excel, XLLs — Dennis M Wallentin @ 4:46 pm

As most of us already know, Microsoft has for the last couple of years built up an enourmus  giant knowledge base about Microsoft Office on the internet. As a consequence it has also become more difficult to navigate around and find the wanted information we are looking for.

However, today I discovered the Great Portal to Microsoft Office knowledge Base. Instead of just saving the URL to my local computer I thought I would make it more available by publishing it here:

Office Development Site Map

One of the key people for the ongoing publishing of Microsoft Office knowledge is Erika Erhli. I have previously related to her so this is a (very good) reminder:

Adventures with Office Products & Technologies

Do You remember how it was during the 90’s? At that time it was rather easy to be updated on a numbers of softwares from Microsoft. Today the softwares have become much more, more complex and more advanced and on top of that the release cycles have become faster.

So I’m glad that I, at least, can keep myself updated of Microsoft Excel. However, in the future we may only be able to keep up with the rapid development for one platform that Microsoft Excel is used on. What do You think?

Kind regards,
Dennis

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

November 8, 2011

Paradigma offer Valentina Office Server 5 connections for free!

Filed under: .NET & Excel, COM Add-ins, Excel, SQLite, Valentina DB, Valentina Office Server, VSTO & Excel — Dennis M Wallentin @ 4:43 pm

Paradigma announced today that they offer their Valentina Office Server 5 connections for free. The offer targets certain groups of users, including Small and Medium sized companies, and for certain tasks.

Not only that, under the same license conditions they also offer Valentina Studio + Valentina Report for free together with any Application Development Kit (ADK) or all ADKs.

The most interesting part of this free offer is, together with the Valentina Office Server itself, the Valentina Report tool. This makes it very attractive as report tools are usually not available for free.

For more information please see the following page at Paradigma’s site:

http://www.valentina-db.com/en/company/news/1-latest-news/257-paradigma-software-releases-free-business-ready-database-and-reports-server

Kind regards,
Dennis

Excel & Valentina Database

Filed under: .NET & Excel, COM Add-ins, Excel, SQLite, Tools, Valentina DB, Valentina Office Server, VSTO & Excel — Dennis M Wallentin @ 1:42 am

Introducing the Valentina Database

I thought I would introduce the Valentina Database (Valentina DB) with this article and discuss how we can connect to it by using ODBC, both in VBA and in VB.NET.

For the last 11-12 months I have been using Valentina DB as an embedded database in all clients’ solutions where it requires some data storage. Some of them run 24/7 and up to this date no errors have been reported related to the database in use. It has mainly been used in managed COM add-ins (Shared add-ins and VSTO add-ins) and by using the .NET driver & APIs for the database.

Using the ODBC technology is still a common way. First of all, it’s a fast way to establish a connection to a data source. It’s also a reliable technology as it has been around for the last 20 years or so.

Actually, we can say exactly the same about the Valentina DB although it has been around “only” about 10 years. In addition, it’s a really fast and compact database with a small footprint. Therefore I find it to be a great companion when I need an embedded database solution. Valentina DB can also be connected to the Valentina Office Server on both x86 and x64 platforms.

Another advantage is, compared with other databases, that it targets a great number of operating system and development platforms. So per se it’s a true cross-platform database. I will later discuss in a coming article more details about developing .NET solutions by using the Valentina DB.

On several development platforms we have access to Valentina APIs library. It’s a quite powerful library. For instance, we can use APIs instead of using SQL the traditionally way, i.e. it’s like LINQ.

Valentina DB is also a maturity software as it has been around since the late 90’s. The general price level should not scare away anyone; expect those that looking for free options.

The company behind Valentina DB, Paradigma Software, offers a good support and maintenance a public mailing list for Q&A. The only thing that I regret is that the Valentina DB Wiki documentation is not yet complete. This is rather typical for small companies; they lack the man hours to keep up the pace with both their product(s) and the documentation to them.

What Paradigma Software says themselves about Valentina DB is:

  • Performs Queries x10 to x500 Faster than Other Databases
  • Convert from Old Relational Databases and Reduce Sizes by 1/3
  • Smart SQL Means Shorter, Easier to Read SQL Queries
  • Modern Post Relational Architecture
  • Connects with All Major Environments on Windows, Linux, Mac OS X
  • Royalty Free Embedded Server Deployment

In addition to the Valentina DB product Paradigma Software also offers related tools such as the Valentina Pro Studio, which supports 18+ Valentina DB platforms. The Studio enables us to work with databases and also to connect to Valentina Office Servers. We can also use their excellent Valentina Reports to create highly customized reports both for Valentina DBs and also for SQLite DBs. Once installed Valentina Reports will be part of the Studio.

Valentina DB Homepage

Valentina DB Wiki

Connecting to a Valentina DB through VBA using ODBC

I have intentionally left out all comments of the code as it should be rather self-explanatory. What we need to do before using the licensed ODBC driver is to register it on the computer which requires us to create a user DSN or a File DSN.

In the first case I use a DSN file as the source for the connection string. It contains the following information:

[ODBC]
DRIVER=Valentina ODBC Driver
Database=C:\Users\Dennis Wallentin\Documents\Valentina DBs\FirstDB.vdb
IsDatabaseLocal=yes

In the second case I create the connection string in code. In both cases I use the Microsoft ADO library in order to work with the database and with its data.

'Make sure You set a reference to
'Microsoft ActiveX Data Objects Library x.x

Option Explicit

Sub Retrieve_Data_ValentinaDB_ADO_FILEDSN()

Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset

Dim wsTarget As Worksheet

Dim stSQL As String

Dim lnCountFields As Long

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset

Set wsTarget = ThisWorkbook.Worksheets("Data")

stSQL = "SELECT * FROM Person"

adoConnection.Open ConnectionString:= _
"FileDSN=C:\Users\Dennis Wallentin\Documents\Valentina.dsn"

adoRecordset.Open Source:=stSQL, ActiveConnection:= _
"FileDSN=C:\Users\Dennis Wallentin\Documents\Valentina.dsn"

lnCountFields = adoRecordset.Fields.Count

Application.ScreenUpdating = False

For lnCountFields = 0 To lnCountFields - 1
wsTarget.Cells(1, lnCountFields + 1).Value = _
adoRecordset.Fields(lnCountFields).Name
Next

wsTarget.Cells(2, 1).CopyFromRecordset adoRecordset

adoRecordset.Close
adoConnection.Close

Set adoRecordset = Nothing
Set adoConnection = Nothing

End Sub

 

Sub Retrieve_Data_ValentinaDB_ADO_ODBC_Driver()

Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset

Dim wsTarget As Worksheet

Dim stSQL As String
Dim stConnection As String
Dim stDataBase As String

Dim lnCountFields As Long

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset

Set wsTarget = ThisWorkbook.Worksheets("Data")

stDataBase = "C:\Users\Dennis Wallentin\Documents\Valentina DBs\FirstDB.vdb"
stConnection = "Driver={Valentina ODBC Driver};IsDatabaseLocal=yes;Database=" & _
stDataBase
stSQL = "SELECT * FROM Person"

adoConnection.Open ConnectionString:=stConnection

adoRecordset.Open Source:=stSQL, ActiveConnection:=stConnection

lnCountFields = adoRecordset.Fields.Count

Application.ScreenUpdating = False

For lnCountFields = 0 To lnCountFields - 1
wsTarget.Cells(1, lnCountFields + 1).Value = _
adoRecordset.Fields(lnCountFields).Name
Next

wsTarget.Cells(2, 1).CopyFromRecordset adoRecordset

adoRecordset.Close
adoConnection.Close

Set adoRecordset = Nothing
Set adoConnection = Nothing

End Sub

Connecting to a Valentina DB through VB.NET using ODBC

The ODBC driver can also be used in VB.NET but it exist a specific version of Valentina that explicit target the .NET platform. In general I recommend using it.

Although the following case shows how we can populate a worksheet with data based on the ODBC datareader I recommend to use classic ADO instead, like above, as we can dump all the data once via the method CopyFromRecordset.

Imports System
Imports System.Data
Imports System.Data.Odbc
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) _
Handles Button1.Click

'Snabbast är att använda classic ADO.

Dim odbcCon As OdbcConnection = Nothing
Dim odbcCom As OdbcCommand = Nothing
Dim odbcDataR As OdbcDataReader = Nothing

Dim stConnection As String = String.Empty
Dim stDatabase As String = "C:\Users\Dennis Wallentin\Documents\Valentina DBs\FirstDB.vdb;"
Dim stSQL As String = "SELECT * FROM Person"

stConnection = "Driver={Valentina ODBC Driver};IsDatabaseLocal=yes;Database=" + stDatabase

Dim stSoureFile As String = "C:\Users\Dennis Wallentin\Documents\Data\SalesData.xlsm"

Try
odbcCon = New OdbcConnection(stConnection)
odbcCom = New OdbcCommand(stSQL, odbcCon)
odbcCom.CommandType = CommandType.Text
odbcCon.Open()
odbcDataR = odbcCom.ExecuteReader(CommandBehavior.CloseConnection)

'In order to dimension the array for field names the number of columns need to be retrieved.
Dim intColumnsCounter As Int32 = odbcDataR.FieldCount - 1

Dim FieldsArr(0, intColumnsCounter) As String

Dim intRowsCounter As Int32 = 0

'The DataReader is a forward-only and read-only recordset. Therefore we cannot
'know in advance the number of records it will return. The only possible solution
'is to estimate the max number of records and use it in the solution.
Dim intMaxRecords As Int32 = 10000

Dim DataArr(intMaxRecords, intColumnsCounter) As Object

'Populate the array of field names by using the DataReader's method 'GetName'.
For intNameCounter As Int32 = 0 To intColumnsCounter
FieldsArr(0, intNameCounter) = odbcDataR.GetName(intNameCounter)
Next

'Populate the array of records by reading all the records in the DataReader.
While odbcDataR.Read
For intRecordCounter As Int32 = 0 To intColumnsCounter
DataArr(intRowsCounter, intRecordCounter) = odbcDataR.Item(intRecordCounter)
Next
intRowsCounter = intRowsCounter + 1
End While

'Close both the connection and the DataReader.
odbcCon.Close()
odbcDataR.Close()

Dim xlApp As Excel.Application = New Excel.Application
Dim wbBook As Excel.Workbook = xlApp.Workbooks.Open(stSoureFile)
Dim wsSheet As Excel.Worksheet = TryCast(wbBook.Worksheets("Data"), Excel.Worksheet)

Dim xlCalc As Excel.XlCalculation = xlApp.Calculation
xlApp.Calculation = Excel.XlCalculation.xlCalculationManual

With wsSheet
.Range(.Cells(1, 1), .Cells(1, intColumnsCounter + 1)).Value = FieldsArr
.Range(.Cells(2, 1), .Cells(intRowsCounter + 2, intColumnsCounter + 1)).Value = DataArr
.UsedRange.Columns.AutoFit()
End With

With xlApp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlCalc
End With

Catch ex As Exception

MessageBox.Show(ex.ToString())

End Try

'Prepare variables for the Garbage Collector.
'Don't forget to do a proper release of alla COM objects.

odbcCom.Dispose()
odbcCon.Dispose()

odbcDataR = Nothing
odbcCom = Nothing
odbcCon = Nothing

End Sub

End Class

Valentina DB offers so much that I sometimes feels that only the sky is the limit. I learn every day something new about the database and its features and the more I learn the more impressed I am.

Enjoy!

Kind regards,
Dennis

Blog at WordPress.com.