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
For c = 1 To .Columns.Count
TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth
copyColumnWidths Sheets("MySheet").Range("A1:AA500"), Sheets("YourSheet").Range("A1:AA500")
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)
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
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
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
If IsNumeric(Range("B1").Value) Then
intStart = Range("B1").Value
If IsNumeric(Range("B2").Value) Then
intEnd = Range("B2").Value
Dim ConnectionString As String
ConnectionString = "Driver=iSeries Access ODBC Driver;System=ISERIES.COMPANY.COM; QUERYTIMEOUT=0;UID=Username;pwd=Password;"
Set Cn = CreateObject("ADODB.Connection")
Cn.ConnectionTimeout = 0
Set Geti5Connection = Cn
Set Cm = CreateObject("ADODB.Command")
Set Cm.ActiveConnection = Geti5Connection
Cm.CommandText = "call mylib.myproc(" & intStart & "," & intEnd & ")"
Set Geti5Command = Cm
Set Rs = Cm.Execute
Set Cm = Nothing