VSTO & .NET & Excel

March 29, 2010

Information About ExcelKB: An Update

Filed under: .NET & Excel — Dennis M Wallentin @ 8:57 pm

Recently I announced that my English site ExcelKB will change its focus and therefore all my Excel related articles would be dropped. Soon after the announcement I was approached by an employee at Microsoft asking if I would be interested to have the articles published at MSDN. Actually, I accepted it immediately although it means limited rights to the material. I have no idea when anything will be published but I’m pleased with the fact that the material will be published. I also admit that I feel proud that my articles qualify to be published at MSDN.

Kind regards,
Dennis

March 26, 2010

New Download Page for XL-Dennis Tools

Filed under: .NET & Excel, .NET Books, COM Add-ins, SQL Server, VSTO & Excel, VSTO Books, XL-Dennis' freewares — Dennis M Wallentin @ 4:15 pm

As a consequence of the reconstruction of ExcelKB my free tools needed a new place. I decided to add a new page to this blog enabling everyone to download the tools:

Kind regards,
Dennis

March 23, 2010

Preserve Size and Location of Windows Forms – Part II

Filed under: .NET & Excel — Dennis M Wallentin @ 8:12 pm

The first post in this mini series can be found here:
Preserve Size and Location of Windows Forms – Part I

Here I will discuss how we must do to preserve the size and location of Windows Forms that are part of managed COM Add-in and VSTO Add-ins. We can still use the approach that was discussed in the first post however we need to add the following code into the closing event of the Windows Form:


    Private Sub frmMain_FormClosing(ByVal sender As Object, _
                                    ByVal e As  _
                                    System.Windows.Forms.FormClosingEventArgs) _
                                    Handles Me.FormClosing

        My.Settings.Save()

    End Sub

As already pointed out by Mike Rosenblum; if we use C# then we need to save the settings for the Windows Form also in standalone applications.

Just for fun I made some additional solutions where we grab a Windows Form’s location and size and use them:

Imports System.Text
'...

    Private Sub btnLocation_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnLocation.Click

        Dim LocationStrBuilder As New StringBuilder("XY         : " + _
                                                    Me.Location.X.ToString + _
                                                    " " + _
                                                    Me.Location.Y.ToString)
        LocationStrBuilder.AppendLine()

        LocationStrBuilder.Append("TopLeft: " + _
                                   Me.Top.ToString + _
                                   " " + _
                                   Me.Left.ToString)

        LocationStrBuilder.AppendLine()

        LocationStrBuilder.Append("Desktop: " + _
                                  Me.DesktopLocation.X.ToString + _
                                  " " + _
                                  Me.DesktopLocation.Y.ToString)

        MessageBox.Show(LocationStrBuilder.ToString)

    End Sub

    Private Sub btnSize_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles btnSize.Click, btnSize.Click

        Dim SizeStrBuilder As New StringBuilder("Height: " + _
                                                Me.Height.ToString)
        SizeStrBuilder.AppendLine()

        SizeStrBuilder.Append("Width  : " + _
                              Me.Width.ToString)

        MessageBox.Show(SizeStrBuilder.ToString)

    End Sub

    Private Sub btnDesktop_Click(ByVal sender As System.Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles btnDesktop.Click

        'Of course, each variable can be set on its own.
        Me.SetDesktopBounds(x:=Me.Location.X + 10, _
                            y:=Me.Location.Y + 5, _
                            width:=Me.Width + 50, _
                            height:=Me.Height + 100)
    End Sub

'...

That’s all for now and in an upcoming post I will discuss anchoring and docking controls on Windows Forms.

Kind regards,
Dennis

March 20, 2010

The COM Shim Wizard for VS 2010 is available

Filed under: .NET & Excel, COM Add-ins — Dennis M Wallentin @ 1:33 pm

Misha Schneerson has now made the Shim Wizard for VS 2010 available for download. What should be noted is that the new version is not officially released by Microsoft and it’s not officially supported. For more information please see COM Shim Wizards for VS 2010.

In view of the fact that VSTO is getting better and better for every new release the need to use managed COM add-ins will decrease. Especially if we only develop for one software within the Office suite. Perhaps this new version of the Shim Wizard will be the last one?

Kind regards,
Dennis

March 19, 2010

Preserve Size and Location of Windows Forms – Part I

Filed under: .NET & Excel — Dennis M Wallentin @ 2:28 pm

Introduction
To preserve size and location of Windows Forms are in many ways a common service towards the end users. In this first part I will take a closer look into how we can remember theses settings between the sessions for standalone applications in VB.NET. In part two and last article I will discuss it for managed COM add-ins and VSTO add-ins.

The Case
First, create a new Windows Form application in VS and we start with the solution for the location of the Windows Form. Actually, it is quite easy to set it up; make sure that the Windows Form is selected and in its properties windows select (PropertyBinding) as the following screen shot shows:

Next, select the button to the right and in the next window select the property Location as the next screen shot shows:

Select (New) in the dialog so that the following dialog is showed:

Name the new Application setting to Location and then click on the OK button. That’s it! To set up the size setting we repeat the above steps but instead of selecting the property Location we select the ClientSize and name it to Size. So whenever the end users change the Windows Form’s location and size the above solution will automatically preserve these two properties.

When we have made the set up and when viewing the Windows Form’s property window we now see two new entries which the screen shot show below also shows:

When selecting the Solution Explore we also note that a new file has been added to the project – app.config.xml – which the following screen shot also reveals:

It is this XML file the changes are being saved into. If we open the XML file we can see the following entries which hold the necessary data:

    <userSettings>
        <WindowsSizeLocation.My.MySettings>
            <setting name="Location" serializeAs="String">
                <value>0, 19</value>
            </setting>
            <setting name="Size" serializeAs="String">
                <value>325, 150</value>
            </setting>
        </WindowsSizeLocation.My.MySettings>
    </userSettings>

Actually, the same information can also be reviewed from the project’s property window as the following screen shot also shows:

In view of the above we can conclude that is quite easy to keep track when size and location are being changed for any Windows Form.

Kind regards,
Dennis

The second post in this mini series can be found here:
Preserve Size and Location of Windows Forms – Part II

March 10, 2010

Deploying Your VSTO add-ins to All Users

Filed under: .NET & Excel — Dennis M Wallentin @ 8:27 pm

The VSTO development team recently announced that they has released a hotfix for Office 2007 so that we now can deploy VSTO solution to “All Users”. For Office 2010 it has been fixed while for Office 2003 it is left out.

For more information please see Deploying your VSTO add-ins to All Users. To get the hotfix for Office 2007 please see A 2007 Office system application does not load an add-in that is developed by using VSTO and Description of the 2007 Office system hotfix package (Mso-x-none.msp): October 27, 2009.

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

March 2, 2010

Populate TreeView Control with Tables, Views and Columns from a SQL Server Database

Filed under: .NET & Excel — Dennis M Wallentin @ 2:41 pm

Introduction
This is the second blog post about populating a TreeView control. In the first blog post, Populate TreeView Control with Tables and Columns from MS Access Database,  I used a MS Access database to populate a TreeView with its tables and related columns. In this blog post I will instead be using a SQL Server Database.

The first example is based on a SQL query and where the retrieved data is than used to populate a TreeView control.

Since it very common that users don’t have access to system data like system tables I also show a scenario where the required information is made available in a XML file. In the second example I use the XMLDataDocument Class to read the XML file in order to populate the TreeView control with required data.

The SQL Query Case
To retrieve the tables, views and their related columns from a SQL Server database we use its schema table information_schema.columns. In other words, we can use a Dataset and to fill it with data we use a SQL query. In the next step we can populate the TreeView control with data. I use the same case as in the first blog post so what is showed below is only the updated code:


Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Form1

Const Con As String = _
"Data Source=DEVELOP;Initial Catalog=AdventureWorks;" & _
"Integrated Security=True"

Const SQLExpression As String = _
"SELECT TABLE_NAME, COLUMN_NAME " & _
"FROM information_schema.columns " & _
"ORDER BY TABLE_NAME,COLUMN_NAME"

Const Database As String = "AdventureWorks"


Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click

Dim Rootnode As TreeNode = Nothing
Dim Mainnode As TreeNode = Nothing
Dim Childnode As TreeNode = Nothing

Dim MainName As String = String.Empty

Dim cn As New SqlConnection(Con)

Dim adp As New SqlDataAdapter(SQLExpression, cn)

Dim ds As New DataSet

adp.Fill(ds, "SystemData")

TVDatabase.Nodes.Clear()

Rootnode = TVDatabase.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)

For Each row As DataRow In ds.Tables("SystemData").Rows

If MainName <> row(0).ToString Then
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=row(0).ToString, _
imageIndex:=1, selectedImageIndex:=1)
MainName = row(0).ToString
End If

Childnode = Mainnode.Nodes.Add(key:="Column", text:=row(1).ToString, _
imageIndex:=2, selectedImageIndex:=2)
Next

TVDatabase.Nodes(0).EnsureVisible()

ds.Dispose()
ds = Nothing
adp.Dispose()
adp = Nothing
cn.Dispose()
cn = Nothing

End Sub

End Class

When we run the code to populate the TreeView control we get the following output:

The XML File Case
In order to set up this case it required a XML file to work with. I created a relevant XML file by using the Dataset in the above case where I used the following snippet code:

ds.WriteXml("c:\SystemData.xml")

The following XML code snippet shows the data in the generated file:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<SystemData>
<TABLE_NAME>Address</TABLE_NAME>
<COLUMN_NAME>AddressID</COLUMN_NAME>
</SystemData>
<SystemData>
<TABLE_NAME>Address</TABLE_NAME>
<COLUMN_NAME>AddressLine1</COLUMN_NAME>
</SystemData>
<SystemData>
<TABLE_NAME>Address</TABLE_NAME>
<COLUMN_NAME>AddressLine2</COLUMN_NAME>
</SystemData>

For reading XML files as well as iterating through their nodes I prefer to use the XMLDataDocument Class which also the below code shows. Initially it may look complex but it’s a rather straightfull way.  I use the same case as in the first blog post so what is showed below is only the updated code:

Imports System.Xml

Public Class Form1

Const Database As String = "AdventureWorks - XML"
Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click

Dim Rootnode As TreeNode = Nothing
Dim Mainnode As TreeNode = Nothing
Dim Childnode As TreeNode = Nothing

Dim MainName As String = String.Empty
Dim NodeName As String = String.Empty
Dim ChildName As String = String.Empty

Dim xmldoc As New XmlDataDocument()
Dim xmlnode As XmlNodeList

TVDatabase.Nodes.Clear()

Rootnode = TVDatabase.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)

'Open and read the document.
xmldoc.Load(filename:="c:\SystemData.xml")

'Node that includes the wanted childnodes.
xmlnode = xmldoc.GetElementsByTagName(name:="SystemData")

For CountNodes As Integer = 0 To xmlnode.Count - 1

'Get the table name.
NodeName = xmlnode(CountNodes).ChildNodes.Item(0).InnerText.Trim()

If MainName <> NodeName Then
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=NodeName, _
imageIndex:=1, selectedImageIndex:=1)
MainName = NodeName
End If

'Get the column name.
ChildName = xmlnode(CountNodes).ChildNodes.Item(1).InnerText.Trim()

Childnode = Mainnode.Nodes.Add(key:="Column", text:=ChildName, _
imageIndex:=2, selectedImageIndex:=2)
Next

TVDatabase.Nodes(0).EnsureVisible()

End Sub

'...
End Class

When executing the code the TreeView control is populated with the wanted data as the following screen shot shows:

That’s all for now and the next blog post will show how we achieve the same tasks as above by using LINQ to SQL and LINQ to XML.

Kind regards,
Dennis

The third article on the subject can be found here:
Populate TreeView Controls with Tables from SQL Server & Access Database by using ADOX

Create a free website or blog at WordPress.com.