Category Archives: Code Examples

Create your own custom InputBox

The InputBox method is handy for asking the user to enter a piece of information and was heavily used back in the VB.Old days. So much so, that I see a number of posts on forums that still use this to get data from the user even when using VB.NET. However, one draw back of the InputBox method is that you cannot determine what button was pressed like you can with a MessageBox. So if you want to determine if the user pressed the Cancel button for example, then the only option is to test if a result was returned (i.e. an empty string or not). But this is not foolproof as the user could have pressed the Ok button without entering any data.

So, if you need an InputBox and you also need to know whether the Ok or Cancel button was pressed, then you will need to roll out your own Input Box.

The following snippet demonstrates creating your own custom InputBox replacement by creating a simple form that associates a dialogResult value to the Ok and Cancel buttons allowing you to check what was pressed.

To start, create a new form in your project called InputBoxForm and add the following controls so that it looks similar to the screen shot below:

CustomInputBox

Control TypeProperties
LabelName: promptLabel
Text: ""
AutoSize: False
TextBoxName: inputTextBox
ButtonName: okButton
DialogResult: Ok
Text: Ok
ButtonName: cancelButton
DialogResult: Cancel
Text: Cancel

Now add the following code to the InputBoxForm:

Note the three properties at the top, this allows us to set the title of the form and the prompt that will be shown in the label that we added. The final property can be read from the calling routine to determine what was entered by the user.

Finally, to use:

Note how the ShowDialog method is used to show the input box. This allows us to test what button was pressed via the DialogResult property. This will be Cancel if the Cancel button was clicked or if the form was closed via the Close box. Otherwise it will be Ok and we can grab the value that the user entered.

Add VBA to Excel file at runtime

This snippet demonstrates how you can add VBA code to an Excel file at run time.

Prerequisites

In this snippet I am using Microsoft Excel 2013 with the Microsoft Excel 15.0 object library.

Before you can use this code, you will have to change a setting within Microsoft Excel to authorise the use of the VBA Project Object Model from external programs. This is a security feature to protect end users from malicious programs and is disabled by default. If you do not enable this feature then the code below will result in an exception stating “Programmatic access to Visual Basic Project is not trusted” as soon as you attempt to reference the VBA environment.

Enabling trust to the VBA Project Object Model

  1. Open Excel and access the Options section.
  2. Open Excel and access the Options section.
  3. Click the Trust Center Settings button
  4. Select the Macro Settings section
  5. Tick the box labelled “Trust access to the VBA project object model”

The code

You will need the following Imports statements:

 

 

Display Excel data in a DataGridView

In this post, I will show you how to use ADO.NET to load data from an Excel file into a DataGridView.

The principle is pretty straightforward, the first thing we will do is ask the user to select an Excel file. Once selected, we will connect to the file using an OleDbConnection object and once connected we will then use the GetSchema method of the OleDbConnection object to load all Worksheets (tables in ADO.NET speak) into a combo box. When the user selects a worksheet name from the combo box we will then select all data from that sheet and display it in a DataGridView by using standard SQL syntax.

So, create a new Windows Form project with any name you like and add the following controls to your form so that it resembles the screen shot below:

Control TypeProperties
LabelName: excelFileLabel
Text: Excel File
Text BoxName: excelFileTextBox
Anchor: Top, Left, Right
ButtonName: openFileButton
Text: ...
Anchor: Top, Right
LabelName: worksheetsLabel
Text: Worksheets
Combo BoxName: worksheetsComboBox
Anchor: Top, Left, Right
Data Grid ViewName: Name: excelDataGridView
Anchor: Top, Left, Right, Bottom
ButtonName: closeButton
Text: Close
Anchor: Bottom, Right

excelDataGridView

Now add the following code to your form:

You can also download a version I made earlier.

Read and Write Images to Access Ole Object Field

This short snippet demonstrates how to write an image (jpg file) to an Ole Object field in Microsoft Access and then how to retrieve that image and display it in a picture box.

First, create an Access database (I was using Office 2013 but earlier versions should work) and a table with one field called pic. Set the field type of the pic field to Ole Object.

Writing the image to the database

The following code write an image to the pic field.

Once you have executed this statement you can check your database table and you should see that the field contains the text “Long Binary Data”, this is our image.

Reading an image from the Ole Object field

This next piece of code will read Long Binary Data value from the Ole Object field and convert it to a byte array. Once converted, the byte array will then be converted to an Image which we can then use to assign to a picture box or use in any other way we like. For this example, add a PictureBox control to your form and then use the following code to read the image:

 

Treeview – checking and unchecking nodes

This code sample demonstrates how to check and uncheck nodes within a Treeview control regardless of what level you are at within the tree. For example, if you check a parent node then all child nodes of that parent will be checked. Likewise, if you have a child node that is not checked and you check it, it’s parent (and it’s parent) will be checked.

The following code is fired via the AfterCheck event of the Treeview control and I have provided both VB and C# samples. The only piece to point out is the use of the RemoveHandler and AddHandler code within the AfterCheck event. This is done to avoid a StackOverflowException being thrown.

VB.NET Sample

C# Sample

 

Using recursion to find a string in all text files

Recently I had a need to work through a large number of text files looking for various bits of information. I decided to write a small program that would at least narrow down the number of files that I had to search through by telling me which files within a directory (and sub directories) contained the string I was looking for.

You can download this solution to use it yourself. Below is just a couple of pointers as to how this works.

The following piece of code performs the search for files within a given directory (and sub directories if the user has checked a box indicating that they want to include sub directories):

Firstly, in order to search for files within a directory, we use the System.IO.Directory.GetFiles method which allows you to specify both the path that you want to search and optionally a file pattern, in this case Text files (*.txt). The syntax is very straightforward and a string array is returned containing the file names matching the files found within the path. Secondly, I have a check to see if the user has selected to include sub directories. If they have, then we make use of the System.IO.Directory.GetDirectories method which returns a string array containing all directories within the given path. Using each path returned I then call the same routine again in order to continue searching within that directory for files and further sub directories. This is known as recursion.

The code above calls out to a method called occurrencesOfSearchTerm which first opens the text file and then reads the entire contents before finally using a very basic regular expression to count the number of occurrences of the search term found within the file:

That’s it. Please download the solution to see the full set of code (not much more than what is posted here) and to test it out for yourself.