Print Debug Messages

It’s really helpful when developing macros in VBA to get some sort of feedback from the code as to what is going on behind the scenes. Doing this has helped me get rid of superfluous reiterations.

1. Show the Immediate window


2. Add descriptive messages to the code

add message for immediate window

3. See the debug messages as they are encountered

read immediate window

Executing an ISeries Stored Procedure from .NET with ODBC

Below is an example of how one might execute a stored procedure on the ISeries from a C# application. I understand that there are certainly other, much shorter ways to achieve this.

using (OdbcConnection DbConnection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MyISeres"].ConnectionString))
using (OdbcCommand DbCommand = new OdbcCommand())
    DbCommand.Connection = DbConnection;
    DbCommand.CommandText = "call MYLIB.MYPROC(?, ?)";

    OdbcParameter p1= new OdbcParameter();
    p1.DbType = System.Data.DbType.String;
    p1.ParameterName = "MyParm1";
    p1.Size = 50;
    p1.Value = "some value";
    p1.Direction = System.Data.ParameterDirection.Input;

    OdbcParameter p2= new OdbcParameter();
    p2.DbType = System.Data.DbType.String;
    p2.ParameterName = "MyParm2";
    p2.Size = 50;
    p2.Value = "some other value";
    p2.Direction = System.Data.ParameterDirection.Input;

    using (OdbcDataReader DbReader = DbCommand.ExecuteReader())
        GridView1.DataSource = DbReader;

Dot or Slash Separator On AS400 JDBC Resource

Recently I had to debug a Java web application and being new to the platform there were quite a few things I was unfamiliar with. One of those things was how to tell an AS400 resource whether it’s libraries and procedures were going to be separated by a slash or a dot. The application I was working on used slashes and apparently my connection’s default was dots. At first I changed all the calls but I always knew there had to be another way.

Here is how I specified what kind of naming the application should use on the server.xml file.

  url="jdbc:as400://AS400URL.COM; libraries= mylib, yourlib, theirlib; naming=system; prompt=false"

Note the naming on the URL property.
“System” causes the connection to interpret the slash character as a separator.


“Sql” causes the connection to interpret the dot character as a separator.


Copy Column Widths

I ran into this code when trying to copy some rows from one sheet to another in Excel through VBA. The widths would always display at the destination spreadsheet’s default column width. This code helps, all you need to pass it is the source and target ranges as parameters.


Private Sub copyColumnWidths(TargetRange As Range, SourceRange As Range)
Dim c As Long
    With SourceRange
        For c = 1 To .Columns.Count
            TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth
        Next c
    End With
End Sub

Example call

copyColumnWidths Sheets("MySheet").Range("A1:AA500"), Sheets("YourSheet").Range("A1:AA500")

Read Word Document Using NetOffice 1.60

I needed to look through a Word document for an application running the .NET 2.0 framework. Here is how I did it using NetOffice 1.60 which can be downloaded via NuGet in Visual Studio.

Imports Word = NetOffice.WordApi
Imports NetOffice.WordApi.Enums

Module Module1

    Sub Main()
    End Sub

    Private Sub readWordDoc()

        ' start word and turn off msg boxes
        Dim wordApplication As New Word.Application
        wordApplication.DisplayAlerts = WdAlertLevel.wdAlertsNone

        ' open document as read only
        Dim myDocument As Word.Document
        myDocument = wordApplication.Documents.Open(System.IO.Path.GetFullPath("MyDoc.docx"), False, True)

        ' get document's content
        Dim content As String
        content = myDocument.Content.Text

        ' close doc

        ' output content 

    End Sub

End Module

How to Create a PDF

Useful function that creates a PDF in VBA.

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
            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, _
        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("") = 2
        .Item("") = ""
        .Item("") = 25
        .Item("") = 1
        .Item("") = smtp_username
        .Item("") = smtp_password
    End With

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

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