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")

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.

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


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
        Exit Sub
    End If
    If IsNumeric(Range("B2").Value) Then
        intEnd = Range("B2").Value
        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