Sunday, November 5, 2017

Crash Course in to Python Programming

I had really been wanting to find a couple of hours to do a crash course on python and finally found an opportunity to do it last night.

Having been a programmer for a while, I'm finding many of the Python videos quite boring as they are for absolute beginners which has you plodding through the basics of programming which makes learning a new programming language painfully slow.

Luckily, I had found this great Python primer video by Derek Banas on YouTube.

I followed along and completed all the code in the video and had working examples of most of the important code snippets for much of how stuff gets done in Python.  Success was not without some pain, see problems and  resolutions below.

Video: Learn Python in One Video



I will save you some pain by telling you there is a very frustrating code issue at the end of this long video.

See code fix below.

When you are in the Dog class referencing the Animal super class from which it inherits the name, weight, height and sound, properties or values you have to use the setter and getter class methods instead of the object "self" reference like we could in the Animal object.  Once you change that, the code works.

For the Dog class, the toString() method only worked for me the example below: (this works) def toString(self): return "{} is {} cm tall and {} kilograms, says {} and owner is {}".format( self.get_name(), self.get_height(), self.get_weight(), self.get_sound(), self.__owner) ...instead of: (this doesn't) def toString(self): return "{} is {} cm tall and {} kilograms, says {} and owner is {}".format( self.__name, self.__height, self.__weight, self.__sound, self.__owner)

As a bonus I would add watching this video to help solidify the idea of polymorphism if you're having difficulty getting a grasp of it.

Hope this helps someone!

Video: What is Polymorphism?



Thursday, October 19, 2017

Saving Currency Values in CSV Files

I searched all over and did not find the right answer so I played around until I had found it myself and now I'll share it with you.

I had picked up a VBA coding project from a contractor who was leaving.

The application takes a bunch of Excel data in various complex formats and creates a comma separated value file (CSV) as output that are used for import into a custom object in Salesforce.

One of the issues the customer was reporting was that they had an expectation that currency value in exported CSV files should retain the 2 digits after the decimal point.

The application was sending the output result in a CSV file but the currency value were only retaining one digit after the decimal if the last decimal was a zero.

A value of 91.20 is coming out as 91.2.

This happens consistently while using Excel with CSV files but not with XLS. It drops the zero every time.

This is how you get around this.
  1. Save your CSV file in MS Excel format with a .xlsx or .xls extension.
  2. Update the "Format Cells" to display the column as Currency. I set the option not to display a $ for currency and 2 digits after the decimal point.
  3. Save this file with a .CSV extension.  Your currency values with the 2 digits after the decimal point are preserved during this conversion.
That is it!

To validate it, open the CSV file with a text editor like Notepad or Notepad++ and you will see the number format with 2 digits after the decimal was retained.

Watch out!  If you open the CSV file with MS Excel and save it again as CSV, Excel will wipe out your currency formatting. 

Hope this helps somebody. 

Regards,
Rick



Friday, September 29, 2017

Salesforce AppExchange: Managed vs. Unmanged Packages

What is the difference between "Managed Packages" and "Unmanaged Packages" in the Salesforce AppExchange?

First, why am I posting Salesforce stuff now?  

I jumped in to a startup that purchased Salesforce and as with many organizations who purchase Salesforce, they didn't have the expertise to develop and get the most out of the product.  Here I am a "Jack of all trades" IT guy so get in there and figure it out.

OK, throw me at the problem. 

I'm currently towards the end of a 6 week Salesforece Admin course and we are on the topic of getting sample reports and dashboards from the Salesforce AppExchange.

Then Admin training seemed to skirt over this topic so I decided to dive in a little deeper.
  • Managed Packages: Sort of like a zip or complied file that contains all the files that make up the package. The contents are hidden from the end user and can't be edited by anyone except the creator, publisher. or consultant.
  • Unmanaged Packages: The contents of the package are not protected and can be edited. You can see all the files that make up the package.
A really big thank you to Juergen Hoehne for posting this video on the subject. It was very helpful.


Hope this helped!

V/R,
Rick Cable


Friday, September 22, 2017

Exporting Your Outlook Calendar to an Excel Spreadsheet Using VBA Macro (2017 Update)

I finally found time to revisit this topic after posting my original article back in 2011.

My skills have much improved and the need for this came up at work so I thought I would have a crack at updating the code and get this out to the people who find value in it.

Back in 2011 I had found and tweaked some VBA code that exports a range of your calendar adding the categories and summing up the time by category.  I used it a lot when I was a Project Manager and Applications Analyst.

Now I'm working at a start-up and there are lots of gaps in reporting. This is where a lot of the old tools I've developed over the years seem to come in handy.


This code is a work in progress and I'll be posting updates along the way.

As of this version.


This is Excel Macro (VBA code) that runs as an Excel Macro which connects to Outlook and extracts calendar items based on a data range the user selects. Returns list of calendar items with the duration of the event in minutes.

I will upload a sample file shortly but the code is updated below. I added update pivot table and pivot chart automatically.

The category part stopped working way back when Outlook 2007 was released so I'll have to see how the code needs to be changed to retrieve categories in the later versions of office. Might add color coding as option as well.

Outlook Calendar Export Version 1.2 Beta

Option Explicit

'Title: Outlook Calendar Export
'Version: 1.2 Beta
'Author: Rick Cable
'Date: 9/22/2017

Sub FindApptsInTimeFrame()
    Dim myStart, myEnd As Date
    Dim Outlook As Outlook.Application
    Dim oCalendar As Outlook.Folder

    Dim oItems As Outlook.Items
    Dim oResItems As Outlook.Items
    Dim oAppt As Outlook.AppointmentItem
    Dim strRestriction As String
    
    Dim strApptCategories
    ' Set 20 as the number of supported categories, should get that number per user's decision.
    Dim strAllCategories(0 To 20) As String
    Dim iTotalCount As Integer
    Dim iDurationPerCategory(0 To 20) As Integer
    Dim strListSep As String
    Dim i, j, iNumApptCategories
    Dim blnExists As Boolean
    Dim dtDiff As Long
    
    'Hard-code the reporting dates just for simplicity in testing.
    myStart = InputBox("Start of Range", "Outlook Exporter", Date)
    myStart = CDate(myStart)
    'StartofRange=cdate(StartofRange)
    myEnd = InputBox("End of Range", "Outlook Exporter", Date)
    myEnd = CDate(myEnd)
    'Original Code
    'myStart = DateValue("01/14/2013")
    'myEnd = DateValue("01/18/2013")
         
    Set oCalendar = Session.GetDefaultFolder(olFolderCalendar)
    Set oItems = oCalendar.Items
     
    'Include all recurring calendar items -
    'master appointments as well as recurring appointments.
    oItems.IncludeRecurrences = True
    oItems.Sort "[Start]"
         
    'Specify the filter this way to include appointments that overlap
    'with the specified date range but do not necessarily fall entirely within
    'the date range.
    'Date values in filter do not explicitly include minutes.
    strRestriction = "[Start] <= '" & myEnd _
    & "' AND [End] >= '" & myStart & "'"
    Debug.Print strRestriction
     
    'Restrict the Items collection.
    Set oResItems = oItems.Restrict(strRestriction)
    'Sort
    oResItems.Sort "[Start]"
     
    'Disabled by Rick: Reformat myStart and myEnd to account for minutes.
    'myStart = #1/14/2013 12:01:00 AM#
    'myEnd = #1/14/2013 12:01:00 AM#
     
    iTotalCount = 0
    'Get the separator between categories from the Windows registry.
    strListSep = WSHListSep()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    Worksheets("Outlook Calendar").Activate
    
    Call DeleteRows
    
    ws.Range("A1").Select
    
    'Write in the header row
    
        ActiveCell.Offset(0, 0).Value = "Subject"
        ActiveCell.Offset(0, 1).Value = "Duration"
        ActiveCell.Offset(0, 2).Value = "Appointment Start"
        
        ActiveCell.Offset(1, 0).Select
    
    For Each oAppt In oResItems
        ActiveCell.Offset(0, 0).Value = oAppt.Subject
        ActiveCell.Offset(0, 1).Value = oAppt.Duration
        ActiveCell.Offset(0, 2).Value = oAppt.Start
        
        Debug.Print oAppt.Start, oAppt.Subject
        Debug.Print oAppt.Duration
        
        ' Get the list of categories specified for this appointment.
        strApptCategories = Split(oAppt.Categories, strListSep)
        iNumApptCategories = UBound(strApptCategories)
        
        ' An appointment that doesn't have a category (with iNumApptCategories being 0) skips this loop.
        For i = 0 To iNumApptCategories
            ' Check if category exists in master array strAllCategories.
            blnExists = False
            If iTotalCount > 0 Then
                ' Master array already has some categories, see if there's a match or should add category
                For j = 0 To iTotalCount - 1
                    If Trim(strAllCategories(j)) = Trim(strApptCategories(i)) Then
                        blnExists = True
                        Exit For
                    End If
                Next
                If blnExists = False Then
                    ' First time this category appears, add category to master array and start tallying time.
                    If iTotalCount >= 20 Then
                        MsgBox "The maximum number of categories has been reached."
                        GoTo Dump
                    End If
                    iTotalCount = iTotalCount + 1
                    strAllCategories(iTotalCount - 1) = Trim(strApptCategories(i))
                    
                    ' Check if the appointment is entirely within the date range.
                    If oAppt.Start >= myStart Then
                        If oAppt.End <= myEnd Then
                            iDurationPerCategory(iTotalCount - 1) = oAppt.Duration
                        Else
                            dtDiff = DateDiff("n", myEnd, oAppt.End)
                            iDurationPerCategory(iTotalCount - 1) = oAppt.Duration - dtDiff
                        End If
                    Else
                        dtDiff = DateDiff("n", oAppt.Start, myStart)
                        iDurationPerCategory(iTotalCount - 1) = oAppt.Duration - dtDiff
                    End If
                Else
                    ' Category already in master array, just tally the time for the category.
                    ' Check if the appointment is entirely within the date range.
                    If oAppt.Start >= myStart Then
                        If oAppt.End <= myEnd Then
                            iDurationPerCategory(j) = iDurationPerCategory(j) + oAppt.Duration
                        Else
                            dtDiff = DateDiff("n", myEnd, oAppt.End)
                            iDurationPerCategory(j) = iDurationPerCategory(j) + oAppt.Duration - dtDiff
                        End If
                    Else
                        dtDiff = DateDiff("n", oAppt.Start, myStart)
                        iDurationPerCategory(j) = iDurationPerCategory(j) + oAppt.Duration - dtDiff
                    End If
                End If
            Else
                ' First category in master array of categories, start master array and start count of categories.
                iTotalCount = 1
                strAllCategories(0) = Trim(strApptCategories(i))
                
                ' Check if the appointment is entirely within the date range.
                If oAppt.Start >= myStart Then
                    If oAppt.End <= myEnd Then
                        iDurationPerCategory(0) = oAppt.Duration
                    Else
                        dtDiff = DateDiff("n", myEnd, oAppt.End)
                        iDurationPerCategory(0) = oAppt.Duration - dtDiff
                    End If
                Else
                    dtDiff = DateDiff("n", oAppt.Start, myStart)
                    iDurationPerCategory(0) = oAppt.Duration - dtDiff
                End If
            End If
        Next
        ActiveCell.Offset(1, 0).Select
    Next
    
    Dim Sheet As Worksheet
    Dim Pivot As Excel.PivotTable
    'Refresh the Pivot table with updated data
    For Each Sheet In ThisWorkbook.Worksheets
        For Each Pivot In Sheet.PivotTables
            Pivot.RefreshTable
            Pivot.Update
        Next
    Next
    
    'List all unique categories and count
Dump:
    For j = 0 To iTotalCount - 1
        MsgBox (strAllCategories(j) & " = " & (iDurationPerCategory(j) / 60) & " hours")
        Debug.Print strAllCategories(j), iDurationPerCategory(j)
    Next
End Sub

Function WSHListSep()
    Dim objWSHShell
    Dim strReg
    strReg = "HKCU\Control Panel\International\sList"
    Set objWSHShell = CreateObject("WScript.Shell")
    WSHListSep = objWSHShell.RegRead(strReg)
    Set objWSHShell = Nothing
End Function

Sub DeleteRows()
Application.ScreenUpdating = False
Sheets("Outlook Calendar").Range("A2:C5000").Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub


Monday, September 18, 2017

Excel Pivot Chart Won't Update with New Data

I had found myself stuck today while working on an Excel Pivot Table and Pivot Chart.

I had made a copy of some source data that I was using for a set of pivot charts.

I decided and needed a subset of that data with duplicates removed and I would create another pivot table and chart based on this new subset of data.

When I create a new pivot table it was still showing me data from the previous data set.

Thanks to the article below, I solved my issue.


Here is the fix.

Go to > PivotTable Options > Data >

Number of Items to retain per Field > None >

Refresh

Thursday, September 14, 2017

Salesforce Administrator Trailhead - Quote Template Setup not Showing after Quote Templates Enabled

This is a very short tip for anyone working on the Salesforce Administrator Trailhead training modules.

I was working on Quotes and Quote settings when I discovered a problem.

The trailhead tells you to enable quotes from the setup menu then create new quote template.

 Guess what, they aren't there.... WTF!

OK, so do this before you pull all your hair out.

LOG OUT of your Salesforce Trailhead then log right back in.

For whatever reason, you must log out of Saleforce after enabling Quotes, then log back in to see them enabled.

Fun stuff. Hope this helps some other poor soul.

Good luck.

~Rick

Friday, September 1, 2017

An Entertaining Look at the New Tech Start-Up Bubble from Dan Lyons (Hubspot and Beyond)

Happy Friday!

I'm in my cube waiting for my Redbull to kick in and thought I would share an entertaining and educational video I found this morning.

Dan Lyons used to be a writer for Newsweek.  After being laid off and 52, he wanted to work at a start-up to see what it was like.  He landed a job at a Boston area tech start-up called HubSpot.

Dan details his experiences there and elaborates on why so many tech start-ups are failing.

I will +1 Dan on this and say is it all about culture. If  you're culture is BS, then you're company is BS.

Enjoy the video.


VIDEO

Thursday, August 31, 2017

What Programming Schenanigans is Rick Up to in August 2017?

This is my August 2017 "Programming Schenanigans" update.

As I can't really talk about my day job much, even though I would love to talk about some of the challenges we face on a daily basis, I will say it has been an amazing 8 months thus far in my new developer role.

I left an engineering support role to help develop resilient technical teams to develop and support some new products.  I took the role knowing it would be chaos but building new teams to do very special work in one of the most challenging domains in IT was a challenge I eagerly accepted. I do love a challenge.

I felt my 20+ year experience at prototyping and attempting startups has come in very handy. I am truly a "Jack of all Trades" IT guy.

In between the multiple projects and taking the Saleforce.com Admin Course (still in it), I managed to find a little spare time to get in some upgrades to my life's work, http://www.finditclassifieds.com.

Upgrade included:

  • HTML and CSS upgrades. Improved experience on Tablet devices.
  • Implementing HTTPS - sites users can now have a secure way to post data.
  • Hardening systems that help the blocking of "Bad Guys"
  • Setup new advertising campaigns to drive more traffic to site
  • Since I've been getting some good traffic and posting from this area lately, I added a new Metro Area for Nashua, New Hampshire. This means site configuration for a new Metro Area and adding DNS entries for the cononical name, http://nashua.finditclassifieds.com then adding the binding in IIS.






Wednesday, August 30, 2017

How to Code the Old ASCII XMas Tree in C# - Repost from my Really Old Blog at RickCable.com

This is a re-post from my very first tech blog post back in 2009 when I was taking programming courses at MJC. This is a classic class project that some may struggle with so here is my very old article on how to code a ASCII XMas Tress in C#.

Introduction to C# Programming: ASCII Xmas Tree - C# and .Net Sample Project


C#, pronounced C Sharp, is a relatively new programming language from Microsoft that was designed to take full advantage of the new .Net Framework.
Below is a sample project I did for a programming class I'm attending at MJC in Modesto. To test and run the code listed on this site you should download a free copy of Microsoft Visual C# Express 2005. There are four Visual Studio Express versions available for free from Microsoft, Visual Basic, C#, J# and C++.
The xmas tree project was a good idea. It really makes you think. I think this project could have been a little more fun for me if I had a little more time but my schedule just won't allow it. I don't see myself coming back to often to update this code but if others want to sent me some other samples I may post them later. So here it goes....
Last updated: Monday, December 4, 2006 8:00 PM

Screen Shot

 
This is what the final result should look like.

The Code

The purpose of this project as I understood it was to write a console program using C#. The goal is to draw a Christmas tree on the screen by making use of arrays and nested for loops. Of course, the one time I missed class all year and that was the day the instructor was discussing this new project. Luckily a fellow student took the time to go over it with me and this is what I came up with. Not sure if it is exactly correct but I'm sure it is close.
Download a copy of tree.exe, the executable file. You can run the code right from here by clicking on it or right click and choose save link as...
using System;

using System.Collections.Generic;

using System.Text;

namespace ConsoleApplication1

{
    class Program

    {

        static void Main(string[] args)

        {

            //create the main array

            int[] myArray = new int[] { 1, 3, 5, 7, 9 };
            //The outside foreach loop to loop throught the array

            foreach (int intLoop in myArray)

            {

                //creates the spaces, takes the array number minus 1 then divide by 2

                //this gives you the amount of spaces needed for each level of the tree

                 for (int iSpace = 0; iSpace < ((myArray[4]-intLoop)/2); iSpace++)

                {

                    System.Console.Write(" ");

                }               

                //middle loop writes the asterisks "*" the full amount of current array[]

                for (int i = 0;i < intLoop; i++)

                {               

                System.Console.Write("*");

             }

                //creates the spaces, takes the array number minus 1 then divide by 2

                //this gives you the amount of spaces needed for each level of the tree

              for (int iSpace = 0; iSpace < ((myArray[4] - intLoop) / 2); iSpace++)

             {

                 System.Console.Write(" ");

             }

            //creates new lines after all 3 loops run

             System.Console.WriteLine("");

            }

            //nest this loop and do it 3 times

            for (int iBase = 0; iBase < myArray[1]; iBase++)

            {

                // now make the base of the tree

                for (int iSpaces = 0; iSpaces < myArray[1]; iSpaces++)

                {

                    System.Console.Write(" ");

                }

                for (int iPipes = 0; iPipes < myArray[1]; iPipes++)

                {

                    System.Console.Write("|");

                }

                // now make the base of the tree

                for (int iSpaces = 0; iSpaces < myArray[1]; iSpaces++)

                {

                    System.Console.Write(" ");

                }

                  //creates new lines after all 3 loops run

                    System.Console.WriteLine("");

            }

        }

    }
}

VBScript: Adding Leading Zeros to a Date - A Repost from my really old blog at RickCable.com

VBScript: Adding Leading Zeros to a Date


This is a re-post from my really old blog at RickCable.com.  I thought that there is still some value for this old code for someone so here it is. Hopefully it indexes better here than on my old site. There is no value in this code if nobody can find it.

I was recently developing a script to loop through an excel file and write the contents in to a text file.  One of the specifications was that the output dates had to have leading zeros like 01/01/2011 but the excel file had them as 1/1/2011.

I thought there must a VBScript function like the built in CDate for or FormatDateTime but neither of these seemed to return dates with leading zeros so I wrote my own function to do it.

As in all programing, there are many ways to write this to get the same output but this way worked for me on the project I used it for.  


Function FixDate(strDate)
Dim iTemp, arrDate, item, strTemp

 ' my custom date fix function
 ' split the date in to an array by the "/" character
 ' check each date item and check to see if it is less than 1000
 arrDate = Split(strDate,"/")
 for each item in arrDate
  if len(item) < 2 then
   item = "0" & item
  end if
  
  ' Next section makes sure there is no / at the end of the rebuilt date when I put it back together.
  if item < 100 then
   strTemp = strTemp & item & "/"
  else
   strTemp = strTemp & item
  end if
 Next
 ' Put the date back together
 FixDate = strTemp
 
End Function

HOW TO CALL THE FUNCTION?


FixDate("1/1/2011") 
 or 
FixDate(YourStringDate)