Thursday 26 July 2012

A bit of script to export emails to excel file




A bit of script to export emails to excel files:
 
<pre class='brush: vb'>
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
Dim strPath As String
Dim intRowCounter As Integer
Dim intColumnCounter As Integer
Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itm As Object
strSheet = "OutlookItems6.xlsx"
strPath = "C:\Test\"
strSheet = strPath & strSheet
Debug.Print strSheet  'Select export folder
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder  'Handle potential errors with Select Folder dialog box.
    If fld Is Nothing Then
        MsgBox "There are no mail messages to export", vbOKOnly, "Error"
        Exit Sub
    ElseIf fld.DefaultItemType <> olMailItem Then
    ElseIf fld.Items.Count = 0 Then
    End If  'Open and activate Excel workbook.

Set appExcel = CreateObject("Excel.Application")

'appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.Workbooks.Add()


wkb.Activate


Set wks = wkb.Worksheets.Add()
wks.Activate

appExcel.Application.Visible = True  'Copy field items in mail folder.
   
    For Each itm In fld.Items
        intColumnCounter = 1
        Set msg = itm
        intRowCounter = intRowCounter + 1

        Set rng = wks.Cells(intRowCounter, intColumnCounter)
        rng.Value = msg.To
        intColumnCounter = intColumnCounter + 1
        rng.Value = msg.SenderEmailAddress
        rng.Value = msg.Subject
        rng.Value = msg.SentOn
        rng.Value = msg.ReceivedTime
        rng.Value = msg.Body

    Next itm

   
wkb.SaveAs (strSheet)
wkb.Close
   
Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing
Exit Sub

ErrHandler:
If Err.Number = 1004 Then
    wkb.Close
    MsgBox strSheet & " doesn't exist", vbOKOnly, _
    Error
Else
    wkb.Close
    MsgBox Err.Number & "; Description: ", vbOKOnly
End If

</pre>

Friday 20 July 2012

Malodic Banjo Arranger Part 1

As part of a push to develop my skills further and raise my visibility within the coding community, I decided to startup a number of open source projects and hopefully leverage existing source on CodePlex, GitHib etc... to take the pain out of getting a project up and running.

Being a part time musician and a long term fan of the Banjo, one of the projects I have begun working on is a Melodic Banjo tablature Arranger implemented either as self contained application or plugin module for an existing  tablature/music scoring application.

A question some may be asking is why does the world need a Melodic Banjo tablature Arranger?  Or, what the heck is Melodic Banjo?  I don't want to go into too much detail as the this Wikipedia article explains things very well but its basically a way of playing a melody on the Banjo which encourages as few sequential notes being played on the same string as possible resulting in an overall un-interupted series of ringing notes.

It is a great style however it is quite time consuming working out the best way to play a given melody in this style as there may be many numbers of ways to play the same note (as with most stringed instruments) but depending on the order of the notes, the current musical phrase and where you last note was fretted it is not immediately obvious what fret & string the next note should be played on.

This I am attempting to help by providing a way to process a MIDI file, pick a track and calculate the best way to arrange the notes in this banjo style.

The second phase would be to allow experimentation of the calculated arrangement allowing the user to fix notes they like and recalculate the remaining 'un-fixed' arranged notes.  I would also like to include the ability to possible open tunings which can be dynamically changed

Finally, the third phase (which is kind of running parallel to the previous two phases) is to wrap the project up in a plug-in to extend an existing app or pull together open source code to display/print the arranged tabliture in a usable format and allow the interaction with the arrangement mentioned in phase 2.

I have had an initial play with nAudio which is great at abstracting phrasing of MIDI files however I have been pleasantly surprised at the development of JavaScript music Score/Midi parses projects on Git Hub:

https://github.com/arthurlenoir/DionyScore
https://github.com/GregJ/HTML5-Guitar-Tab-Player
https://github.com/cwilso/Standard-MIDI-File-reader

I think this might end up being a web based application which I didn't expect.

Monday 28 May 2012

Virtual Machines Duplicate VHDs

Great little trick with Virtual Machine HDs.  I like others have found it very useful to create a virtual disk and duplicate it for different things.  The problem is if you attempt to re-use a copied vhd for a new machine in VirtualBox then it causes a nice little error:

VBoxManage.exe: error: Cannot register the hard disk [VHD Drive name] {[GUID]} because a hard disk  [VHD Drive name]   with UUID { [GUID]} already exists

The fantastic command to sort out this little problem is "VBoxManage internalcommands sethduuid" e.g:


C:\Program Files\Oracle\VirtualBox>VBoxManage internalcommands sethduuid "Z:\TestMachine1\HardDisk.vdi"

Which should result in something like:

UUID changed to: c9f4029b-8782-4348-82f3-0d2243ead38c


Thanks to the helpful individuals on these sites which give more examples:
http://michail.flouris.net/2011/11/virtualbox-vm-disk-clone-uuid-problem/
https://forums.virtualbox.org/viewtopic.php?p=33678

Friday 24 February 2012

Getting back in the Saddle

I have recently got back in to Cycling and have been replacing sitting in traffic for 45mins with sitting on a saddle for 60mins.  The way I look at it I should save some cash, loose some weight and it certainly puts me in a better mood for the day.

Some great resources for planning routes are at : http://www.betterbybike.info/cycle-maps.  Firstly, order or print the maps as they are fantastic and free! secondly, the cycle trip planner is a great way to get bike friendly routes planned quickly!

Have fun and ride safe!!

Tuesday 7 February 2012

PL/SQL generate PDFs

Something I have been meaning to play about for quite a while is the use of a number of free PL/SQL libaries for creating PDF documents on the fly in PL/SQL code.  Some the libraries out there are :

http://reseau.erasme.org/PL-FPDF,1337
http://technology.amis.nl/blog/8650/as_pdf-generating-a-pdf-document-with-some-plsql

For completeness, there is a paid libary http://plpdf.com/ but as it costs I'm not going to play with it for the moment.

I'm going to have a bit of a play with the end result of storing the resulting PDF as a BLOB in a table for use in a document library.

Update to follow!

Friday 13 January 2012

Sharepoint URL Multi Filter example

After a little playing about to create drop down selects to filter a sharepoint list (There are many examples on the net) I hacked together my way to acheve a multi select.  Partly to stop myself from fogetting and to share with the community, below is a little javascript code + HTML extract to be used in Content Editor Web Part in Sharepoint 2007 for a multiple drop down select:

<script type="text/javascript">
    function docLib() {

        var docLibUrl = "/sharepoint/testpage.aspx";

        var combo1 = document.getElementById("Select1");
        var select_val1 = combo1.options[combo1.selectedIndex].value;

        var combo2 = document.getElementById("Select2");
        var select_val2 = combo2.options[combo2.selectedIndex].value;

        var combo3 = document.getElementById("Select3");
        var select_val3 = combo3.options[combo3.selectedIndex].value;

        var filtercount = 1;

        var txt = document.getElementById("Text1");
        var filterstring = "";
        var filterField = "DocIcon";

        var Url_String;

        //Need to add filter count based on the number of filters selected

        if (select_val1 != "") {
            filterstring = "FilterField" + filtercount + "=Business&FilterValue" + filtercount + "=" + select_val1 + "&";
            filtercount += 1;
        }


        if (select_val3 != "") {
            filterstring = filterstring + "FilterField" + filtercount + "=Business%5Fx0020%5FOwner&FilterValue" + filtercount + "=" + select_val3 + "&";
            filtercount += 1;
        }

        if (select_val2 != "") {
            filterstring = filterstring + "FilterField" + filtercount + "=Status&FilterValue" + filtercount + "=" + select_val2;
            filtercount += 1;
        }


        Url_String = docLibUrl + "?" + filterstring;

        document.location = Url_String;
        txt.value = Url_String;
    }

</script>
<table>
<tr><td>Business:</td>
<td>
<select style="margin:10px; 0px;" id="Select1">
<option value="">--Select--</option>
<option value="FilterVal1.1">FilterVal1.1</option>
<option value="FilterVal1.2">FilterVal1.2</option>
<option value="FilterVal1.3">FilterVal1.3</option>
</select></td>

<td>Status:</td>
<td>
<select style="margin:10px; 0px;" id="Select2">
<option value="">--Select--</option>
<option value="FilterVal2.1">FilterVal2.1</option>
<option value="FilterVal2.2">FilterVal2.2</option>
<option value="FilterVal2.3">FilterVal2.3</option>
</select></td>


<td>Business Owner:</td>
<td>
<select style="margin:10px; 0px;" id="Select3">
<option value="">--Select--</option>
<option value="FilterVal3.1">FilterVal3.1</option>
<option value="FilterVal3.2">FilterVal3.2</option>
<option value="FilterVal3.3">FilterVa31.3</option>
</select></td>
<td>
<input id="Button1" type="button" value="Filter" onClick="docLib();"/></td>
</tr>
</table>

Monday 9 January 2012

Create VHD during install of Win 7/2008

Create VHD using Windows 7/2008 install process

  1. Boot your machine with a blank hard drive using the Windows 7 DVD.
  2. Click next on the language screen if English is appropriate.
  3. SHIFT+F10 to launch a WinPE command console. Although the commands below are in upper case, the commands are not case sensitive. I am only using upper case for readability here.
  4. Enter DISKPART to run the utility.
  5. LIST DISK to see the available disks in your system. I am assuming a single raw disk.
  6. SEL DISK 0
  7. CREATE PARTITION PRIMARY
  8. FORMAT FS=NTFS QUICK
  9. LIST VOL
  10. ASSIGN
  11. LIST VOL
  12. CREATE VDISK FILE=c:\windows7rc.vhd MAXIMUM=200000 TYPE=EXPANDABLE
  13. SELECT VDISK FILE=c:\windows7rc.vhd
  14. ATTACH VDISK
  15. CRE PART PRI
  16. FORMAT FS=NTFS QUICK
  17. ASSIGN LETTER=V:
  18. LIST VOL
  19. LIST VDISK
  20. exit DISPART
  21. exit WinPE command console
  22. Install to the newly created 200GB (fuzzy math) virtual hard disk which looks just like a partition to Windows Setup
  23. Repeat steps 11-18 for the Windows Server 2008 R2 VHD but with the obvious changes for the vdisk filename, size, etc.
Thanks http://blogs.technet.com/keithcombs/


Alternative is (If windows 7 already installed):
1. Create VHD
2. Mount VHD Drive
3. Copy install ISO 7/2008R2 into VHD mounted drive
4. Retart machine and F8 into windows 7 repair
5. From CMD use DISK part to mount VHD Drive
6. CD to mounted VHD drive and run setup
7. Select VHD drive during setup to install to the VHD drive

Finialy the tool to manage vhd images such as size : http://archive.msdn.microsoft.com/vhdtool