Reading a delimited file to copy and delete attachments with .NET

This video expands on a previous video and reads the attachment information from a delimited file instead of an Excel spreadsheet. It also shows how to keep a list of missing and renamed files and display those in the application.

Subscribe to My Channel


My Book: Creating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More


Reading an Excel file to automate the copying and deletion of attachments with .NET

In this video, we build a .NET Windows Forms application to programmatically read a column in an Excel worksheet, manipulate it to remove HTML and format its file names, and bind that to a grid. We then programmatically find each file and copy it to a new directory. Finally, we optionally delete the original files.

Subscribe to My Channel


My Book: Creating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More

Simple Programming Concepts Implemented in PowerApps, Web Forms, HTML, Windows Forms, and ToolBook

In this video, we build the same simple application in Visual Studio (Windows Forms, Web Forms, HTML), ToolBook, and PowerApps to see the differences in environments in working with properties, events, and methods.

Subscribe to My Channel

My Book: Creating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More

Mac/Xcode/Objective-C Thoughts for a Longtime Windows/Visual Studio/Visual Basic User

I used an Apple II in high school and then had a Mac when I ran the computer center at the Air Force Research Laboratory at Edwards Air Force Base in the early 1990’s. But other than that and my iPhone, I have been on the Windows/Microsoft stack. However, creating iOS applications like Music Genius requires a Mac. Almost a year ago, I bought a MacBook Pro on Cyber Monday. Here are a few random observations on the journey.

  • I used Apple’s Boot Camp to run Windows 8.1 as well. The process is fairly involved but the documentation is good. It required me to buy an OEM Windows license, but I’ve been very happy with the result. I can run Office 365, Visual Studio 2012/2013, SQL Server 2012, QuickBooks, etc. when I boot to Windows. I use Microsoft SyncToy to automatically back up both my Windows and Mac data to an external drive.
  • I have a Microsoft Arc Touch Mouse that works for both the Mac and Windows sides, though I need to resync if I want to move from one to another. With the proper settings, I am efficient on both sides with the touch pad as well. To get right-click to work on Windows with the touchpad, you need to enable that in Control Panel – Boot Camp. With the MacBook keyboard, the Delete key works like Backspace in Windows. When I am in my office, I connect an external USB keyboard that has both keys. I use keyboard shortcuts extensively and switching between the Control key (Windows) and the Command key (Mac) takes some getting used to. When doing development, I’ll switch between my desktop (Windows) and Mac numerous times during the day. I’m lucky if I do Ctrl + C to copy on Windows and Command + C to copy on the Mac and not the reverse.
  • In my work environment, I have two 29″ LG monitors for my desktop machine. I have the MacBook to the left of those two monitors. I connect it to the left-most LG monitor with an HDMI – DVI-D cable. That allows me to get the optimal 2560 x 1080 resolution on the LG monitor. I switch the input on the LG monitor to whichever computer I am currently using so that I have two monitors either way (one being the MacBook’s screen). I have two keyboards and two mice that I swap back and forth on my desk. I leave my right-most monitor showing my Windows desktop so that I can respond to email, do web searches, etc.
  • On the software side, both Apple and Microsoft subsidize their development platforms. Apple’s Xcode is totally free. Microsoft’s Visual Studio has a cost for the Professional version that I use, but it is pretty nominal. Apple’s Mac and iOS Developer programs each cost $99 per year while Microsoft’s Windows Store and Windows Phone Developer programs are both free.
  • I must admit to being pleasantly surprised by the capabilities and features of Xcode. Its simulators for various iPhones and iPads are awesome. You are also able to connect your iPhone and debug your code while it is executing on the phone. Some tasks that are easy for me in Windows are sometimes a struggle in the Mac environment, but considering I have been using versions of Visual Studio for 15 years and have used Xcode for less than 4 months, it has not been too bad.
  • The biggest challenge has been the Objective-C language. Even on the Windows side I am more comfortable with Visual Basic and OpenScript that C#. I’ve increasingly done more JavaScript/jQuery over the years and thus am more comfortable with what I used to call “squiggly bracket” languages, but Objective-C was still a big change. I won a free month’s subscription on Pluralsight at our local .NET User’s Group and I found their tutorials very helpful. There is also a strong user community where you can usually find someone who has encountered your issue or challenge. I found a number of tutorials at quite useful as well.
  • Apple has recently come out with its new Swift language, which is much closer to JavaScript, ActionScript, and even Visual Basic. I used Swift for the Slide Show Soundtrack. There are some growing pains with it and Xcode, however, and I’ve gone back to Objective-C for the Mac store version of Music Genius.

Responsive Web Design with Exam Engine and Training Studio

Responsive Web Design is a fairly new concept with the idea being that we want our web content to adjust to different browser capabilities and, in particular, viewport or screen sizes. So rather redirecting to a different set of content when accesses by an iPhone, iPad, or Android device with a smaller screen, we adjust our content accordingly. Ethan Marcotte coined the phrase and is one of its biggest proponents. Here are two articles that we found helpful: and We first learned about the concept in this excellent book:

Making Exam Engine and Training Studio content responsive is challenging in the sense that you have to deal with both the background (index.htm) and the templates shown within the iFrame. But having templates rather than individual pages of training makes it quite a bit easier as there are a limited number of templates to fine-tune. The approach we used is to adjust the background elements both horizontally and vertically so they fit with both small and large screen sizes. With the templates, we only adjust them horizontally. This keeps the templates from running over the navigation buttons and so forth when they are resized[1]. Here is a matching template from Exam Engine at full size:


Here it is again at a width of about 520 pixels:


Notice that we moved the Previous and Next buttons
to the left of the screen. The status text (not shown) is now below those
buttons and above the audio controls (which the items within the template have
sized automatically so they still fit on the screen). The drag and drop
functionality still works fine, though with this particular template the
“reset” functionality only operates correctly
when the user has not resized the screen while this template is

Here is the same screen at about 390 pixels. It
still looks good but any smaller and we start running into problems with the
support text wrapping into the question text and so forth. Notice that we have
moved the exam name to its own line so that it doesn’t overlap the “Question 10
of 10” text. We have also moved the “countdown timer” right next to the Score
Exam button so that it doesn’t get cut off.


Here is an even smaller screen size (290 pixels). We have moved the support text to its own line[2]. It now wraps and gets in the way of the question text. But some minor adjustments on the length of the text or omitting the support link altogether would take care of that. Long feedback and status text also causes problems at such a small resolution. But even without these adjustments, we’ve gone a long way towards making our content quite viewable at multiple screen sizes.


The general approach to making this work is to first leave the style sheets as described above alone until we get to a width below our default size (800 pixel width for the templates that come with Exam Engine). We then use a CSS media query to adjust from there. It is important to understand that these CSS settings then stay in place until either another media query kicks in (such as at 400 pixels or whatever) or if the user resizes her browser above the value[3].


There are multiple media queries. At the first one (800 pixels width), we change all left and width values to the corresponding percentage values. As discussed in the references above, this uses the all-important formula: result = target / context. In our case, the context is our width, typically 800 pixels. The target is the existing amount in pixels. So if we have left: 8px;, we change it to left: 1%;, since 8 / 800 = 1%. Here are the media queries with some comments in line.

@media screen and (max-width: 800px)
	     left: 2.5%; /* 20px (target) / 800px (content) = 0.025 - 2.5%*/

	     left: 31.25%;

	     left: 53.75%;

	/* rest omitted for space reasons */

@media screen and (max-width: 768px)
	/* We switch the left and width of the status label and the various buttons to a fixed pixel amount so that they won’t move further or get too thin. */

	     width: 235px; 

	     left: 250px; 

	     left: 310px; 

	#previousImageBtn, #emailResultsImageBtn 
	     left: 318px; 

	/* rest omitted for space reasons */


The templatestyles.css and the individual style sheets for the templates have similar media queries. Of particular interest is the handling of images and media that are designed to fill a particular portion of the screen. Here is the media query from hotobjects.css.

@media screen and (max-width: 791px)
	#answer_1, #answer_2, #answer_3, #answer_4, #answer_5, #answer_6, #answer_7, #answer_8
		max-width: 23.75%;

		left: 23.75%;

		left: 23.75%;

		left: 47.5%;

		left: 47.5%;

		left: 71.25%;

		left: 71.25%;

		width: 55.375%;

We use the max-width setting to ensure that the images (answer_1, answer_2, etc.) scale down as we reduce the screen width. On an 800 pixel width screen, the images are designed to be a maximum of 190 pixels wide. So we use our result = target / context formula to come up with 23.75% = 190 / 800. Note that this query starts at 791 pixels rather than 800. That is because the iFrame itself is 792 pixels wide and testing revealed that we needed a 1-pixel offset.

[1] Another way the media query could go away or change to a different value is if the user changes the orientation of the device, such as going from portrait to landscape.

[2] All of these screen shots have a spot for the student name, which accounts for the space at the top of the screen.

[3] This can still happen if you have long feedback or lots of text. If you are deploying to smaller devices like iPhones, we recommend either limiting question feedback or adjusting backgroundstyles.css to further push down the navigation buttons.

Creating a Windows Presentation Foundation Slide Show

As described in the previous post on ActionScript, I wanted to create an application for the “Hack Ack” that would dynamically read photos and music from subdirectories and display them in a multimedia slide show. In addition to the Flash/AIR version, I wanted to create a Silverlight version. However, just like Flash in a browser would not have sufficient permissions to read the local hard drive, Silverlight in a browser could not either. So I decided to use the similar Windows Presentation Foundation (WPF). The complete application can be downloaded as part of the Archives for Attendees, but here are the main components.

I wrote the AIR one first and knew it wouldn’t take very long to duplicate the logic. There are few nuances (timers) and different capabilities (Generics), but the logic is largely the same. The design is again to read all the photos into one collection and all the music into another. We randomly grab a music file and start playing it. We handle its “completed” event and launch a new one when the music file is finished. We then start a timer to use with the photos. When the timer fires, we randomly pick a photo, remove it from the collection, and display the photo. Once all the photos are finished, we stop the timer. To store information between timer cycles, we need to declare some variables outside a function block as shown below.

Private photoDelay As Integer = 3 ' seconds
Private photoList As List(Of FileInfo)
Private musicList As List(Of FileInfo)
Private timerId As DispatcherTimer

We use photoDelay in our timer. Note that it is in seconds here rather than milliseconds. The two List(Of FileInfo) variables are Generics that are similar to the ArrayCollection variables used in the AIR example but which allow us to specific the type of object (FileInfo) being stored. I could have used the similar Vector concept in AIR if I wanted. However, it lacks the removeItemAt method that was very helpful. The timerId variable stores the reference to our timer. Notice that this is a DispatcherTimer. I first used a normal Timer and found out that it runs on a background thread in WPF. That was a problem since a background thread cannot update the user interface thread.

When the application starts, we call the configureApp function as shown below:

Private Sub configureApp()
	Dim startingDir As String = AppDomain.CurrentDomain.BaseDirectory
	Dim photoDirectory As New DirectoryInfo(String.Format("{0}\photos", startingDir))
	Dim musicDirectory As New DirectoryInfo(String.Format("{0}\music", startingDir))
	Dim photoFileList As FileInfo() = photoDirectory.GetFiles()
	Dim musicFileList As FileInfo() = musicDirectory.GetFiles()

	photoList = photoFileList.ToList()
	musicList = musicFileList.ToList()
End Sub

We use the DirectoryInfo class to find our two subdirectories (“photos” and “music”) and then get a listing of all the files in them. We store them in our associated photoList and musicList variables.

The code for the “Start” button is shown below:

Private Sub startBtn_Click(ByVal sender As Object, ByVal e As _
    System.Windows.RoutedEventArgs) Handles startBtn.Click

	Dim timerId As New DispatcherTimer() ' use DispatcherTimer rather 
	    ' than Timers.Timer to avoid threading issues

	With timerId
	    .Interval = New TimeSpan(0, 0, 0, photoDelay)
	    AddHandler .Tick, AddressOf timerHandler
	End With
End Sub

We create our timer and then set its Interval property to be a TimeSpan that we create from our photoDelay variable (3 seconds). The next line is where we tell WPF what handler (timerHandler) to call when the timer fires. We then start the timer and call the playSoundhandler below.

Private Sub playSound()
	If musicList.Count > 0 Then
		Dim rand As New Random()
		Dim indexNum As Integer = rand.Next(0, (musicList.Count - 1))
		Dim musicFileId As FileInfo = musicList(indexNum)

		AddHandler mediaPlayer.MediaEnded, AddressOf soundCompletedHandler
		mediaPlayer.Source = New Uri(musicFileId.FullName, UriKind.Absolute)

		statusLabel.Text = String.Format("Playing {0}. indexNum = {1}. Count = {2}.", _
		    musicFileId.Name, indexNum, musicList.Count)
	End If
End Sub

Private Sub soundCompletedHandler(ByVal sender As Object, ByVal e As EventArgs)
	playSound() ' plays next sound
End Sub

We first check to make sure that we have music files to play. If so, we create a Random object. We then call its Next method to give us a number between 0 and the number of file (again -1 to account for starting from 0). We then grab the reference to the associated music file. We associated our MediaPlayer object’s MediaEnded event with our soundCompletedHandler handler. To play the sound, we just set the Source property to a Uri that is basically the URL to the file. Notice how similar this is to the URLReqest that we used in ActionScript. Very importantly, we remove the file from the musicList. This is how we avoid playing the file again and how we know to stop when all the files have been played. Finally, we set the text of our status label to be the information on the name of the file, the index number, and count of the file.

The last piece of the puzzle is displaying the photos. That happens in the timerHandler handler shown below:

Private Sub timerHandler(ByVal sender As Object, ByVal e As EventArgs)
	If photoList.Count > 0 Then
		Dim rand As New Random()
		Dim indexNum As Integer = rand.Next(0, (photoList.Count - 1))
		Dim photoFileId As FileInfo = photoList(indexNum)
		Dim sourceId As New BitmapImage(New Uri(photoFileId.FullName, UriKind.Absolute))

		photoImage.Source = sourceId

		statusLabel.Text = String.Format("Displaying {0}. indexNum = {1}. Count = {2}.", _
		    photoFileId.Name, indexNum, photoList.Count)
		If timerId IsNot Nothing Then
		End If
	End If
End Sub

This logic is very similar to that used to play the music files. We again get a random number between 0 and the number of photos still available (-1 to account for the fact that we are starting at 0). We get the complete path (FullName property) and set the source property of our Image control to a Uri built from that path. We remove the file from the collection and update our status label. Once we are done, we stop the timer.

If you would like to see how to implement this application in Adobe AIR and ActionScript, see the previous post.

Visual Basic in Reports

I’ve been spending a great deal of time building reports in Visual Studio for our new Tracker Reports product. While SQL Server Reporting Services has a number of great features, one of my favorites is the fact that you can embed Visual Basic code in the report itself. This can be extremely helpful with complex logic. For example, the completion status of a lesson or course can be expired, critical, or alert when it has an expiration date. Here is what the function looks like:

Public Function DetermineCompletionStatus(ByVal completionStatusObj As Object, _
    ByVal expirationDateObj As Object, ByVal currentDays_CriticalObj As Object, _
    ByVal currentDays_AlertObj As Object, Optional ByVal isCourse as Boolean = True) As String

    Dim completionStatus As String
    Dim expirationDateId, currencyCriticalDate, currencyAlertDate As DateTime
    Dim currencyCriticalDays, currencyAlertDays As Integer
    Dim checkExpiration As Boolean = isCourse
    Dim defaultStatus as String = "Not Started"

    If isCourse = False Then
    	defaultStatus = "not attempted"
    End If

    If IsDBNull(completionStatusObj) = True Then
        completionStatus = defaultStatus
        completionStatus = CStr(completionStatusObj)
        If completionStatus = ""
        	completionStatus = defaultStatus
        End If
    End If

    Dim returnString As String = completionStatus

    If IsDBNull(expirationDateObj) = True OrElse expirationDateObj Is String.Empty OrElse _
        IsNothing(expirationDateObj) = True OrElse expirationDateObj.ToString = "" Then

        expirationDateId = DateTime.MinValue
            expirationDateId = Convert.ToDateTime(expirationDateObj)
        Catch ex As Exception
            expirationDateId = DateTime.MinValue
        End Try

        If expirationDateId > DateTime.MinValue Then
            If IsDBNull(currentDays_CriticalObj) = True OrElse IsNothing(currentDays_CriticalObj) = True OrElse _
                currentDays_CriticalObj.ToString = "" OrElse currentDays_CriticalObj.ToString = "-10" Then

                currencyCriticalDays = 0
                currencyCriticalDate = expirationDateId
                currencyCriticalDays = CInt(currentDays_CriticalObj)
                currencyCriticalDate = expirationDateId.AddDays(-(currencyCriticalDays))
            End If

            If IsDBNull(currentDays_AlertObj) = True OrElse IsNothing(currentDays_AlertObj) = True OrElse _
                currentDays_AlertObj.ToString = "" OrElse currentDays_AlertObj.ToString = "0" OrElse _
                currentDays_AlertObj.ToString = "-10" Then

                currencyAlertDays = 0
                currencyAlertDate = expirationDateId
                currencyAlertDays = CInt(currentDays_AlertObj)
                currencyAlertDate = expirationDateId.AddDays(-(currencyAlertDays))
            End If
        End If
    End If

    Select Case completionStatus.ToLower
    Case "completed", "passed"
	' expiration Date

	checkExpiration = True
    End Select

    If checkExpiration = True Then
        If expirationDateId <> DateTime.MinValue Then
            If (expirationDateId <= Now) Then
                Dim expiredString As String = "Expired"

                returnString = expiredString
            ElseIf (currencyCriticalDate <= Now) Then
                returnString = "Critical"
            ElseIf (currencyAlertDate <= Now) Then
                returnString = "Alert"
            End If
        End If
    End If

	returnString = StrConv(returnString, VbStrConv.ProperCase)
    Return returnString
End Function

Within the report itself, we can use an expression for the value for the “status” rather than linking to just a single column from our query. For example, here is an expression for the course status:

=Code.DetermineCompletionStatus(Fields!CompletionStatus.Value, Fields!ExpirationDate.Value, _
    Fields!CurrencyDaysFlag_Critical.Value, Fields!CurrencyDaysFlag_Alert.Value)

Notice how we begin the expression with Code to denote that we are using our own code rather than built-in operators.

Dictionaries in .NET

In the previous post, we looked at a question related to the Dictionary object in ActionScript. Here is some continued information on the equivalent object in .NET.

We are looking at a Silverlight version of Training Studio 2 right now (note: this was true when written. But we have since decided to have the next version be HTML and JavaScript). One advantage in .NET is that we can use Generics to specify the type of objects within the Dictionaries or ArrayCollections. For example, here is how the same objects are defined in ActionScript and Silverlight:


public static var masterContentArray:ArrayCollection = new ArrayCollection();
public static var pageArray:Dictionary;


Friend masterContentDictionary As New Dictionary(Of Integer, Dictionary(Of String, String))
Friend pageArray As Dictionary(Of String, String)

Note that we renamed masterContentArray to masterContentDictionaryand made the “page number” an explicit key. The direct equivalent to the ArrayCollection would be:

Friend masterContentArray As New List(Dictionary(Of String, String))

The advantage of Generics is that we can specify exactly what kind of objects are stored in the Dictionary or List objects. If we try to put an object of the wrong type in it, Visual Studio will tell us. When we pull objects out of the Dictionary or List, we can use them as that type right away, rather than having to use the Dictionary() syntax.