Move selected data into table

I've been building reports for a client and found myself copy and pasting data into tables, I was extracting about 120 cells worth of data from a performance test and pasting it into a formatted table in groups of 20; doesn't sound like a big deal but do it for some 100 tables and it adds up. So naturally i wrote a neat little excel macro to do so.

Sub Populate_Response_Times_1_User()
    Dim RowStart As Integer
    RowStart = 3
 
    'move data from selected column of cells into columns
    Call Populate_Response_Times(RowStart)

    'style the columns of data
    For i = 1 To 5
        Call StyleDataColumn(i * 2, RowStart, i Mod 2 = 1)
    Next i
End Sub
 
Sub Populate_Response_Times_25_Users()
    Dim RowStart As Integer
    RowStart = 26
 
   'move data from selected column of cells into columns
    Call Populate_Response_Times(RowStart)

    'style the columns of data
    For i = 1 To 5
        Call StyleDataColumn(i * 2, RowStart, i Mod 2 = 1)
    Next i
End Sub

Sub Populate_Response_Times_50_Users()
    Dim RowStart As Integer
    RowStart = 49

  'move data from selected column of cells into columns
    Call Populate_Response_Times(RowStart)

    'style the columns of data
    For i = 1 To 5
        Call StyleDataColumn(i * 2, RowStart, i Mod 2 = 1)
    Next i
End Sub

Sub Populate_Response_Times(RowStart As Integer)
    Dim ColumnIndexMultiplier As Integer
    Dim RowIndex As Integer

    For i = 0 To Selection.count
        If (i Mod 20 = 0) Then
            'use next column
            ColumnIndexMultiplier = ColumnIndexMultiplier + 1
            'restart rows
            RowIndex = 0
        End If
         ' select appropriate cell and set it's value to the selected itertor
        Cells(RowStart + RowIndex, 2 * ColumnIndexMultiplier).Value = Selection(i + 1)
        RowIndex = RowIndex + 1
    Next i
End Sub


Sub StyleDataColumn(Column As Integer, Row As Integer, Green As Boolean)
    'select column and row range to modify
    ActiveSheet.Range(Cells(Row, Column), Cells(Row + 19, Column)).Select

    'format look of cells
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
    Selection.Font.Italic = True
 
    If (Green) Then
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End If
End Sub

there are three macro's

• Populate_Response_Times_1_User()
• Populate_Response_Times_25_Users()
• Populate_Response_Times_50_Users()

Each one of these leverages the same internal logic it just specifies the insert row to a different value

Home Page response time for 1 user on test
0 to 5 min		5 min to 10 min		10 min to 15 min		15 min to 20 min		20 min to 25 min		25 to 30 min
00:15	2.06	05:15	2.10	10:15	2.13	15:15	2.06	20:15	2.08	25:15	2.32
00:30	2.19	05:30	2.06	10:30	2.17	15:30	2.27	20:30	2.16	25:30	2.27
00:45	2.11	05:45	2.03	10:45	2.10	15:45	2.10	20:45	2.31	25:45	2.36
01:00	2.12	06:00	2.28	11:00	2.09	16:00	2.03	21:00	2.07	26:00	2.61
01:15	2.04	06:15	2.08	11:15	1.90	16:15	2.13	21:15	2.21	26:15	2.29
01:30	2.08	06:30	2.05	11:30	1.99	16:30	2.06	21:30	2.34	26:30	2.26
01:45	2.03	06:45	2.40	11:45	2.44	16:45	2.18	21:45	2.61	26:45	2.61
02:00	2.09	07:00	2.22	12:00	2.13	17:00	2.15	22:00	2.00	27:00	2.28
02:15	2.07	07:15	2.08	12:15	2.14	17:15	2.09	22:15	2.30	27:15	2.65
02:30	2.00	07:30	2.11	12:30	2.23	17:30	2.29	22:30	3.81	27:30	3.19
02:45	2.06	07:45	2.28	12:45	2.16	17:45	1.99	22:45	2.50	27:45	4.53
03:00	1.97	08:00	2.19	13:00	2.11	18:00	2.04	23:00	2.08	28:00	5.86
03:15	1.98	08:15	2.28	13:15	2.12	18:15	2.05	23:15	1.97	28:15	4.86
03:30	2.07	08:30	2.20	13:30	2.14	18:30	2.06	23:30	2.25	28:30	4.36
03:45	2.01	08:45	2.13	13:45	2.12	18:45	2.28	23:45	2.82	28:45	3.87
04:00	2.02	09:00	2.30	14:00	2.23	19:00	2.05	24:00	2.41	29:00	2.19
04:15	3.14	09:15	2.09	14:15	2.27	19:15	2.15	24:15	3.63	29:15	2.13
04:30	2.16	09:30	2.29	14:30	2.15	19:30	2.05	24:30	1.97	29:30	1.96
04:45	2.25	09:45	2.12	14:45	2.05	19:45	2.21	24:45	2.23	29:45	2.54
05:00	2.15	10:00	2.13	15:00	2.04	20:00	1.92	25:00	2.22	30:00	2.42

In the excel file we have 3 of the above tables with the 3 rows to start at are 3, 26 and 49.

Toasts 03 Background

So as we all know UWP apps just like there predecessors only run when they are in the foreground, this is to ensure performance of the device, preserve battery-life, etc... not sure what else that's why i put etc. Anyway we do have a way to run some logic in the background, now a caveat is that there is no guarantee that you're background task will ever fire, if the user has put their device into quite hours or the system is constrained for resources etc...

to start we have to add new winRT component project to our solution

so our solution explorer should look like

now that we have our two project ready let's open up the Class1.cs file in the newly added pc.background-taskExample project.

Now let's change it from Class1 to something a little more descriptive let's rename Class1 to LaunchToastBackgroundTask.

We are also going to implement the IBackgroundTask interface, which only implements one method; and it's Run, this is the method that will asynchronously fire. I mention Asynchronously because we're going to have to grab a deferral to keep our Background task from falling out of scope before we fire our code.

using Windows.ApplicationModel.Background;

namespace pc_backgroundTaskExample

{

    public sealed class LaunchToastBackgroundTask : IBackgroundTask

    {

        public void Run(IBackgroundTaskInstance taskInstance)

        {

            var deferral = taskInstance.GetDeferral();

            //Our Logic goes here

            deferral.Complete();

        }

    }

}

Now let's create the logic to fire a toast notification from our background task

using Windows.ApplicationModel.Background;

using Windows.UI.Notifications;

namespace pc_backgroundTaskExample

{

    public sealed class LaunchToastBackgroundTask : IBackgroundTask

    {

        public void Run(IBackgroundTaskInstance taskInstance)

        {

            var deferral = taskInstance.GetDeferral();

            var template = ToastTemplateType.ToastText02;

            var xmlDoc = ToastNotificationManager.GetTemplateContent(template);

            var xml = xmlDoc.GetXml();

            /*  <toast>

                    <visual>

                        <binding template="ToastText02">

                            <text id="1"></text>

                            <text id="2"></text>

                        </binding>

                    </visual>

                </toast>*/

            var textElements = xmlDoc.GetElementsByTagName("text");

            textElements[0].AppendChild(xmlDoc.CreateTextNode("Title text"));

            textElements[1].AppendChild(xmlDoc.CreateTextNode("body text"));

            var toast = new ToastNotification(xmlDoc);

            var notifier = ToastNotificationManager.CreateToastNotifier();

            notifier.Show(toast);

            deferral.Complete();

        }

    }

}

just as before

next let's move to our application, the first thing we have to do is enable background tasks in the app manifest.

once that's done let's go to our main application and write the logic of when to fire our background task.

using pc_backgroundTaskExample;

using System;

using System.Linq;

using Windows.ApplicationModel.Background;

using Windows.UI.Xaml;

using Windows.UI.Xaml.Controls;

namespace pc.toastExample

{

    public sealed partial class MainPage : Page

    {

        public MainPage() { this.InitializeComponent(); }

        private async void Button_Click(object sender, RoutedEventArgs e)

        {

            var allowed = await BackgroundExecutionManager.RequestAccessAsync();

            switch (allowed)

            {

                case BackgroundAccessStatus.AllowedMayUseActiveRealTimeConnectivity:

                case BackgroundAccessStatus.AllowedWithAlwaysOnRealTimeConnectivity:

                    var existing = BackgroundTaskRegistration.AllTasks

                        .FirstOrDefault(t =>

                            t.Value.Name == nameof(LaunchToastBackgroundTask)).Value;

                    if (existing != null)

                        existing.Unregister(false);

                    var builder = new BackgroundTaskBuilder();

                    builder.Name = nameof(LaunchToastBackgroundTask);

                    builder.TaskEntryPoint = typeof(LaunchToastBackgroundTask).ToString();

                    builder.SetTrigger(new SystemTrigger(SystemTriggerType.InternetAvailable, false));

                    var taskRegistration = builder.Register();

                    break;

                case BackgroundAccessStatus.Denied:

                    //denied

                    break;

                case BackgroundAccessStatus.Unspecified:

                    //cancelled

                    break;

            }

        }

    }

}

now our background task will fire when our device receives an internet connection.