How to Create a PDF

Useful function that creates a PDF in VBA.

Usage:
Creating a PDF from the active worksheet.

    Dim myFile As String
    'Save PDF being generated to Temp location
    myFile  = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp")) & "myPDF.pdf"
    fileToSend = Create_PDF(ActiveWorkbook.ActiveSheet, myFile, True, False)

The code:

Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
                 OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
    Dim FileFormatstr As String
    Dim Fname As Variant

    'Test to see if the Microsoft Create/Send add-in is installed.
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

        If FixedFilePathName = "" Then
            'Open the GetSaveAsFilename dialog to enter a file name for the PDF file.
            FileFormatstr = "PDF Files (*.pdf), *.pdf"
            Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
                  Title:="Create PDF")

            'If you cancel this dialog, exit the function.
            If Fname = False Then Exit Function
        Else
            Fname = FixedFilePathName
        End If

        'If OverwriteIfFileExist = False then test to see if the PDF
        'already exists in the folder and exit the function if it does.
        If OverwriteIfFileExist = False Then
            If Dir(Fname) <> "" Then Exit Function
        End If

        'Now export the PDF file.
        On Error Resume Next
        Myvar.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=OpenPDFAfterPublish
        On Error GoTo 0

        'If the export is successful, return the file name.
        If Dir(Fname) <> "" Then Create_PDF = Fname
    End If
End Function

Sending Email Through SMTP (Using CDOSYS)

Very handy function for sending emails through SMTP using VBA.

Sub Mail_SMTP(StrAttachment As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
    
    On Error GoTo ErrHandler
    
    Dim smtp_username As String
    Dim smtp_password As String
       
    smtp_username = ""
    smtp_password = ""
    
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
    
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mycompany.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = smtp_username
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = smtp_password
        .Update
    End With

    With iMsg
        Set .Configuration = iConf
        .To = StrTo
        .CC = ""
        .BCC = ""
        .From = getUsername() & "@mycompany.com"
        .Subject = StrSubject
        .TextBody = StrBody
        .AddAttachment StrAttachment
        If Send = True Then
            iMsg.Send
        Else
            iMsg.Display
        End If
    End With
    
    If Err.Number = 0 Then
        MsgBox "Email sent to: " & StrTo, vbInformation, "Email"
    End If
    
ErrExit:
    Exit Sub

ErrHandler:
    MsgBox "Unable to send email. Please make sure you are connected to the internet."
    Resume ErrExit
    
End Sub

Sending an Email Through Outlook

This handy function sends an email through Excel using VBA.

Sub Mail_Outlook(StrAttachment As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
    
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error GoTo ErrHandler
    
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .Body = StrBody
        .Attachments.Add StrAttachment
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    If Err.Number = 0 Then
        MsgBox "Email sent to: " & StrTo, vbInformation, "Email"
    End If

ErrExit:
    Set OutMail = Nothing
    Set OutApp = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox "Description     : " & Err.Description & vbNewLine & _
           "Error Number    : " & Err.Number, vbOKOnly, "Error"
           
    Resume ErrExit
    
End Sub

Setting Tab Order In Excel

I found a couple of different ways to do this but by far this was the less obtrusive one.

Please note that the left section of the formula checks your current location and the right section dictates your next location.

Private Sub Worksheet_Change(ByVal Target As Range)
    'jump B2->B3->B4->B5->B6->B7
    If Target.Cells.Address = "$B$1" Then Range("B2").Select
    If Target.Cells.Address = "$B$2" Then Range("B3").Select
    If Target.Cells.Address = "$B$3" Then Range("B4").Select
    If Target.Cells.Address = "$B$4" Then Range("B5").Select
    If Target.Cells.Address = "$B$5" Then Range("B6").Select
    If Target.Cells.Address = "$B$6" Then Range("B7").Select
End Sub

Executing a DB2 Stored Procedure from Excel with Parameters

Recently I had to help someone by creating a small piece of VBA code that would fetch rows returned by an ISeries stored procedure. The stored procedure however required two parameters to be set; a from date and a to date. Since this took some time to research I thought I’d post a simplified version on here for myself as well as anyone who needed the reference.

Note: It is highly recommended that you use parameterized queries and not simply a concatenation of values. https://support.microsoft.com/en-us/kb/181734

Code Comment

The following code gets an user input start date (in YYYYMMDD format) from cell B1 and an end date from cell B2. It then places those dates into the stored procedure as parameters. Any resulting rows (with the exception of headers) are displayed starting from cell A6.

An important thing to note here is the use of

QUERYTIMEOUT=0

in the connection string. Using this keeps your procedure call from timing out.

Additionally note how instead of using a loop, rows returned are simply placed from the recordset to cells A6+ using

Range("A6").CopyFromRecordset Rs

The Code

Sub UpdateQuery()
    
    If IsNumeric(Range("B1").Value) Then
        intStart = Range("B1").Value
    Else
        Exit Sub
    End If
    
    If IsNumeric(Range("B2").Value) Then
        intEnd = Range("B2").Value
    Else
        Exit Sub
    End If
    
    Dim ConnectionString As String
    ConnectionString = "Driver=iSeries Access ODBC Driver;System=ISERIES.COMPANY.COM; QUERYTIMEOUT=0;UID=Username;pwd=Password;"
    
    Dim Cn
    Set Cn = CreateObject("ADODB.Connection")
    Cn.ConnectionTimeout = 0
    Cn.Open ConnectionString
    Set Geti5Connection = Cn
    
    Dim Cm
    Set Cm = CreateObject("ADODB.Command")
    Set Cm.ActiveConnection = Geti5Connection
    Cm.CommandText = "call mylib.myproc(" & intStart & "," & intEnd & ")"
    Set Geti5Command = Cm
    Set Rs = Cm.Execute
    
    Range("A6").CopyFromRecordset Rs
    Set Cm = Nothing

End Sub

How to Make a Quick and Dirty ArrayList in PHP

At work I usually work with .NET. My own projects however, tend to be PHP for the most part. Tonight I needed to mimic .NET’s ArrayList object. Since PHP does not have such an object (that I know of) I decided to cook one up for myself. My example depicts a book (object) and a shelf (array) scenario for easier comprehension.

<?php
// Holds all our books
Class Shelf {
	private $ar = array();
	public function add($book) {
		array_push($this->ar, $book);
	}
	public function listBooks() {
		return $this->ar;
	}
}
// Book class, kept simple
Class Book {
	public $title;
	public $author;
}

// Declare a new book and add details
$book1 = new Book;
$book1->title = "Death of a Salesman";
$book1->author = "Arthur Miller";

// Declare another book and add details
$book2 = new Book;
$book2->title = "Fight Club";
$book2->author = "Chuck Palahniuk";

// Add the books to our book shelf
$shelf = new Shelf;
$shelf->add($book1);
$shelf->add($book2);

// Take a peek inside the book shelf
print_r($shelf->listBooks());
// TIP: JSON output anyone?
echo json_encode($shelf->listBooks());
?>

Convert Date and Time to UTC

I have dealt with PHP dates and times in the past but it has never been this difficult and time consuming to pick back up.

This week I needed to change a timestamp from Eastern standard time to a UTC for storing in my database. Woah, did this tax my brain!

Anyways here are only four ways (I am sure there are more) I found for changing a date and time to a UTC timestamp.

1. [PHP] Using mktime()

date_default_timezone_set('America/New_York'); 
$date = mktime(0,0,0,7,16,2013); // Construct date to generate a UNIX timestamp (integer)
echo gmdate("Y-m-d H:i:s", $date); // Alternatively date('Y-m-d H:i:s',$date);

2. [PHP] Using strtotime()

date_default_timezone_set('America/New_York'); 
$date = strtotime('2013-07-16 00:00:00'); // Construct date to generate a UNIX timestamp (integer)
echo gmdate("Y-m-d H:i:s", $date); // Alternatively date('Y-m-d H:i:s',$date);

3. [MySQL] Using FROM_UNIXTIME()

This works on-the-fly when inserting your date into the database. And like the above it needs the integer representation of the UNIX timestamp.

date_default_timezone_set('America/New_York'); 
$date = strtotime('2013-07-16 00:00:00'); // Construct date to generate a UNIX timestamp (integer)
$SQL = "INSERT INTO table_name (id, created) VALUES (1,FROM_UNIXTIME({$date}))"; // MySQL does the conversion when you insert the row

4. [MySQL] Using UTC_TIMESTAMP()

This works quickest but only inserts the current date and time into the database. Simple but useful.

$SQL = "INSERT INTO table_name (id, created) VALUES (1,UTC_TIMESTAMP())"; // MySQL does the conversion when you insert the row

Realizations

While changing the integer version of an UTC timestamp to the human readable, SQL version and wasting so much time. I came to a realization, maybe one should store the UTC timestamp in integer form AS IS and convert it on the fly during presentation. Its smaller and easier to compare, who knows, maybe to avoid a little server processing by doing the presentations conversion in JavaScript. Ah well, at least this adventure helped (sort of) remind me of how time conversions works in PHP. But boy are there way too many functions for dealing with it.

Append JavaScript Tag Dynamically

Sometimes you need to append a specific JavaScript file based on the user’s browser or as a result of some equation. Here is a little handy function that will help you do this.

function appendScriptTag(src) {
       var jstag = document.createElement('script');
       jstag.setAttribute('type', 'text/javascript');
       jstag.setAttribute('src', src);
       document.getElementsByTagName("head")[0].appendChild(jstag);
}

Remove JavaScript Tags From HTML

After a while of trying to create the perfect regular expression that would remove script tags from HTML I came across this super simple regular expression. It goes to show you, sometimes the simplest things work best.

s = Regex.Replace(s, "", "",RegexOptions.Singleline|RegexOptions.IgnoreCase);

Source: http://forums.asp.net/t/1323604.aspx/1

Secure Session ID Cookies on IIS6 Using Metabase And Classic ASP

I have been looking for a solution to this problem for weeks! How do you mark a cookie as secure so that it will only be transmitted via an SSL connection in IIS6? The first (and best) solution is so simple that I am annoyed by the amount of time I wasted on workarounds (solution 2).

Please keep in mind these are two different solutions. I suggest using the first one or the second one (if you have to) but not both together.

Solution 1

  1. Enable Direct Metabase Editing- As it mentions this allows you to edit the Metabase.xml file while running IIS.
    1
  2. Navigate to and open the Metabase.xml file.
    2
  3. Change the value of the property ASPKeepSessionIDSecure to equal “1” instead of “0”. Note the number must be in quotes.
    3

More Metabase Properties: http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/0d49cbc8-10e1-4fa8-ba61-c34e524a3ae6.mspx?mfr=true

Solution 2

I do not suggest this approach as it seems to require an extra trip to the server for the cookie to be marked secure. In which if you think about it, still leaves you vulnerable to being packed sniffed. The attacker can get your session cookie prior to you logging in and since the same cookie gets marked secure, he could then use the insecure cookie with your same session ID to hijack your session. This can be mitigated however by requiring SSL for the directory the user is browsing.

Nonetheless here is the code that marks your cookie secure. It must be included on every page through a sort of server side include.

<%
'///////////////////////////////////////////////
' secureAspSessionCookie
''''''''''''''''''''''''''''''''''''''''''''''''
' Notes:
'	If on SSL takes asp session cookie and marks it secure. If user changes
'	to non-SSL, new cookie is issued.
'////////////////////////////////////////////////
Function secureAspSessionCookie() 
    If Request.ServerVariables("HTTPS") = "on" Then         
        Dim AspSessionCookie
        AspSessionCookie = Request.ServerVariables("HTTP_COOKIE")
        If len(AspSessionCookie) > 0 Then
            AspSessionCookie = "ASPSESSIONID" & Split(AspSessionCookie,"ASPSESSIONID")(1)
            If InStr(1, AspSessionCookie, ";") then
                AspSessionCookie = Split(AspSessionCookie, ";")(0)                     
            End If
            Response.AddHeader "Set-Cookie", AspSessionCookie & "; Secure; Path=/"  
        Else
            Response.redirect(Request.ServerVariables("URL"))     
        End If
    End If
End Function
    secureAspSessionCookie() 
%>

Source: http://stackoverflow.com/questions/2099777/classic-asp-how-to-check-if-aspsessionid-cookie-has-been-marked-as-secure