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

November 19, 2013

Add-in Express Series for Excel Beginners

Filed under: .NET & Excel, Apps for Office, COM Add-ins, Excel, SQL Server, Tools, Valentina DB, VSTO & Excel, XLLs — Dennis M Wallentin @ 7:51 pm

Add-in Express have published a great number of interesting articles for Excel beginners. To be more specific, Excel beginners who use their All-in Framework .NET to create COM Add-ins, RDTs and Functions Libraries (XLLs and UDFs).

Here is the link to the first article in the series:
http://www.add-in-express.com/creating-addins-blog/2013/09/23/excel-addin-development-application-base-objects/

Enjoy!

Kind regards,
Dennis

April 7, 2013

Time to move on – Exit MVP Program

Filed under: .NET & Excel, .NET Books, Apps for Office, COM Add-ins, Excel, SQL Server, VSTO & Excel, VSTO Books, XLLs — Dennis M Wallentin @ 4:27 pm

Around 2005 – 2006 I peaked with MS Excel, since then I have slightly moved away from it year to year. In 2010 I was honored to become part of Microsoft’s MVP-program.

Of course, it was interesting to get another position and more closed to Microsoft in general, the production team in particularly. However, I never got excited about it as I was moving away from MS Excel. In addition, I didn’t put much efforts to support the online community in various forms.

Given the circumstances I finally took the decision to not be up for the next renewal process. Looking back I can conclude that it has been an amazing time, from the 80’s and until now. I have also achieved more than what I thought was possible. In other words, I have nothing more to proof. So I can walk away and looking ahead for new adventures in the world of softwares.

But before I close the MS Excel book I have one thing I would like to point out and to discuss. Let me first conclude that Microsoft have never really been loyal to the group of developers for MS Excel. It’s regretful as the group have a strong commitment and interest of developing MS Excel further.

In the beginning we got the macro language, XLM, which we started to use more and more. Then VBA and VB6 came and Microsoft asked us to drop XLM in favor of these two Basic languages. 10 years later VB6 was depreciated and since then VBA also risk to be depreciated. Microsoft asked us to replace them with .NET and VSTO. 10 years later .NET and VSTO face the same situation as VB6, i.e to be depreciated. Microsoft now ask us to start develop with Apps for Office. Given the short history, i.e about 20 years, it’s remarkable the number of changes Microsoft have done.

In my opinion, Microsoft’s trust capital is now below zero due to lack of loyalty Microsoft show the group of MS Excel developers. The question is not about Apps for Office rather what will come next?

But it’s no longer of interest for me. I’m moving along and I set focus on other tools and platforms. The blog will change its name and extend its contest with other tools including other platforms. Actually, I will go back to VB6, pick up new tools like PowerBasic and PureBasic which will allow me to write everything in code, including the UI.  Other tools I have picked up is Real Studio and LiveCode together with my two favorites databases; Valentina and Ninja Pro.

I have uninstalled Office 2013 and replaced it with the 2000 version. I may re install it later on in case I find Apps for Office interesting unless it has been already replaced with something new!

It has been a fantastic time in my life to be part of the online Excel community and be part of an exciting time for MS Excel. My English has been improved with >90 %.  I have some good friends around the globe . So without mention any names I would finally say:

Thank You all!

Kind regards,
Dennis

March 28, 2013

Combinatorics Using Excel Formulas

Filed under: .NET & Excel, COM Add-ins, Excel, XLLs — Dennis M Wallentin @ 8:45 pm

I have the honor to know Roberto Mensa via Linked In. He is strongly committed to explore, develop, improve and to discuss formulas in Excel. The site is fully dedicated to Excel formulas. If You’re serious about MS Excel then this site is a must.  Of course, he is not alone, another great contributor is Krisztina Szabó aka Kris.

The virtual spot has the name E90E50 fx. I have no ideas for what it stand for…

Anyway, You may start at the following point: E90E50 fx.

The link points to the main page. However, the latest contribution is Combinatorics using Excel formulas.

Make sure You have some hours free to take part of the contribution. Don’t forget to bring coffee to the computer.

Enjoy and have a nice Easter Weekend!

Kind regards,
Dennis

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

December 7, 2012

Tools for MS Excel 2013 Development

Filed under: .NET & Excel, Apps for Office, Excel, Tools, VSTO & Excel, XLLs — Dennis M Wallentin @ 2:12 pm

I just want to add an article that gives the URLs for downloading the SDK and the Toolkit for Apps for Office with Visual Studio 2012. It’s not easy to keep up the pace and find the required tools so this is also a reminder to myself.

Download:

Enjoy!

Dennis

November 10, 2012

Axialis Pure Flat 2013 Toolbar Stock Icons Rock!

Filed under: .NET & Excel, COM Add-ins, Excel, Real Basic Developing, Tools, UI Design, VSTO & Excel, XLLs — Dennis M Wallentin @ 2:00 am

A software program’s success is usually depended on its functionality, i.e. it does the work and it has a user-friendly UI. In fact, the UI is the most important part of applications as that is what the end-users see and work via. This is something we all known (or at least should know).

Among the components that make up the UIs are icons. Icons should give the users an understanding about the functions that are hidden behind the icons. When Microsoft changes the UI for its Windows operating system it also affect the design on icons.

With Windows 8 we have got a new UI style, the Metro style. The new style of icons can simple be named as flat.

Axialis, the vendor of the tool IconWorkshop, offers also excellent icons. At least that’s my opinion, therefore I would like to push for their new series of Pure Flat icons.

The below Ribbon UI shows some of the flat icons in Axialis first series of icons:

For more information and to see more icons examples please visit:

Kind regards,
Dennis

March 29, 2012

VLOOKUP Week; Using VLOOKUP in VB

Filed under: .NET & Excel, COM Add-ins, Excel, VSTO & Excel, XLLs — Dennis M Wallentin @ 3:49 pm

Bill Jelen, MrExcel, has marked this week to honor the VLOOKUP function in MS Excel. Around the online Excel development community You will find contributions, usually blog posts, that gives various aspects for using or not VLOOKUP.

I thought it would be interesting to create a case and see how VLOOKUP can be part of  VB-solutions. Actually, for several years ago I did similar solutions but with classic VB (VB6) for a customer. Imagine that we have a price list for our products. Because of the frequent price updates the company has decided to store it in a MS Excel workbook. To keep it very simple for the sales staff they use a standalone VB-solution where they select products, retrieve the price for each product item and add some additional information manually to get a total price.

As most Excel developers should know, we can acquire data from workbooks in several ways. The most common approach is to treat the workbook as a flat database table. Technically we either use classic ADO or DAO to connect to the database table and using a SQL query to get the wanted records. This is a smooth solution but it assumes that the ADO/DAO library is available on all computers that will use the solutions. To avoid a situation where we must check if the library is installed or not we can use a solution where we automate Excel and execute the VLOOKUP function to get the wanted data. Practical we get a robust solution that is not depended on ADO/DAO and when working with small data sets the performance is fully acceptable.

The following screen shot shows how the price list workbook looks like:

As we can see it’s a simple table that is manually updated.

The next screen shot shows the VB-solution in action. The product list is picked up from a storage system. Sales person starts to select the products he/she wants to calculate the prices for, retrieve the prices, add number of items and then get a total price per product. The total prices including freight etc. is done in another part of the VB-solutions:

As You can see it’s a simple tool to work with. The last piece of the solution is the code I use to retrieve the prices and populate the DatagridView with.

The following code is in use and is executed when clicking on the Get Price button control:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Public Class Form1

Private Sub btnVLOOKUP_Click(sender As System.Object, e As System.EventArgs) _
Handles btnVLOOKUP.Click

Dim Location As String = Environment.GetEnvironmentVariable("USERPROFILE") _
.ToString() + "\Documents\Product Lists\"

Dim File As String = "'" + Location + "[P_List_2011.xlsx]ProductList'!R1C2:R78C3,2,0"

Dim itemChecked As Object

If Me.CheckedListBox1.CheckedItems.Count = 0 Then
MessageBox.Show("Please select at least one product.")
Exit Sub
End If

Dim xlApp As New Excel.Application

Try

For Each itemChecked In Me.CheckedListBox1.CheckedItems

Dim Price As String = String.Empty

'Here we do the lookup based on the selected product name.
Price = xlApp.ExecuteExcel4Macro("VLOOKUP(""" + itemChecked.ToString() _
+ """," + File + ")")

Dim rowValues As String() = New String() {itemChecked.ToString, Price.ToString}

'Add the row with data to the data grid,
Me.dgwPList.Rows.Add(rowValues)
Me.dgwPList.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

Next

Catch ex As Exception

MessageBox.Show(ex.ToString())

Finally

xlApp.Quit()

End Try

'Cleaning up.
If Not xlApp Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
xlApp.Quit()
End If

Marshal.FinalReleaseComObject(xlApp)

End Sub

Edit: I intentionally used the XLM macro program to show that we can still use it and with VB.NET. I thought that someone would point out that it can also be done by using xlApp.WorksheetFunction.VLookup. My friend Ken Puls, ExcelGuru, finally pointed it out.
Kind regards,
Dennis

February 14, 2012

From VBA to VB.NET using ExcelDna

Filed under: .NET & Excel, Excel, Tools, XLLs — Dennis M Wallentin @ 1:25 am

This is a notification about a recent published article by Patrick O’Beirne on his blog. Patrick has compiled a list of resources, for native Excel developers, to develop User Defined Functions (UDFs) with ExcelDna in VB.NET.

For some years ago several developers in the online Excel community made quite often critical comments and rant a lot about using .NET for developing Excel solutions.

Today the opinions are slowly changing to a more positive review of .NET which I welcome very much.

Here is the link to Patrick’s article: From VBA to VB.NET using ExcelDna

Of course, it exist other tools as well. For instance Add-in Express for Microsoft Office & .NET, which is a RAD tool that allows us to develop many different .NET solutions for Excel. Personally I prefer this tool than any other, it does all the hard work and allow me to set focus on the core in .NET solutions. It’s also the most powerful RAD tool and is unique.

Kind regards,
Dennis

Older Posts »

Blog at WordPress.com.