CopyFromRecordset and Transpose

[pjleitner]

I am trying to transpose the data that I am bringing in from a recordset from rows to colomns. The code below currently pastes the data verticaly. Any suggestions would be great.

    For Each Box_Number In Box_NumberList
        With rsUnits
            ' Assign the Connection object
            .ActiveConnection = cnUnits
            ' Extract the time stamp
            .Open "SELECT [RunNumber] FROM [PFDBMirror].[Abound].[Box] WITH(NOLOCK) INNER JOIN [dbo].[UnitDisposition] WITH(NOLOCK) ON [UnitDisposition].[BoxID] = [Box].[BoxNumber] Where [Box].[BoxNumber]='" & Box_Number.Value & "';"
            ' Copy the Run Number into cells Q:2 on Sheet2
            Sheet2.Range("Q" & Box_Number.Row).CopyFromRecordset rsUnits
 
            .Close
        End With

 

[xenou]

Since your recordset is one column, I would use the .GetRows method of the recordset object here. That will give you an array, which you can write to the cells with something like:

Pseudocode:
Cells(x,y).Resize(number of rows,1).Value = array

If the dimensions come out wrong then you can play with the size of the destination, or try using application.transpose blah blah blah. I'm fuzzy on the details right now. Just watch your ubounds - the array will probably be 0-based but the count of the cells in the destination range might be one based. Sorry - I'm not being very detailed here but I hope this will be enough to get you pointed in the right direction. If not post back and we'll get a more concrete example for you.

 

[pjleitner]

Thanks for the help. I was starting to think I wasn't going to get any.

I'm still learning and I am still a bit fussy on your suggestion. If you could help clarify, that would be awesome!

Can't the transpose/pivot be done in the SQL statement before it is stored in the recordset?

 

[xenou]

Here's an example with recordset.GetRows. The rows are 0-based and in a column,row matrix - which I find rather odd. It may be that this is what you want anyway, since you are trying to transpose your results (I might be transposing a transposition and getting you back where you started - in which case, switch the two arguments in the resize property and then remove the = application.transpose(a) and just use = a)

Dim a As Variant
If Not rs.EOF Then
    a = rs.GetRows
    Sheet1.Cells(1, 1).Resize(UBound(a, 2) + 1, UBound(a, 1) + 1).Value = Application.Transpose(a)
End If

Can't the transpose/pivot be done in the SQL statement before it is stored in the recordset?

I don't know if this could be worked out on the SQL end - probably some way or another yes. SQL doesn't have a single native command to transpose a result set (not that I know of, anyway).

 

[pjleitner]

Thanks for the reply. Unfortunately I am still having issues implementing your recommendations. Not sure where to insert the code and what changes are needed to work with my file.

I am surprised something as simple as this and with only one column involved is so difficult.

Current output:
<TABLE style="WIDTH: 137pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=182><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=20 width=84>Box Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 width=98>Run Number</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>101012190</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01D2010066</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01C2010075</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01A2010069</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01B2010060</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01C2010075</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01D2010066</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01C2010075</TD></TR></TBODY></TABLE>

Would like output to columns:
<TABLE style="WIDTH: 581pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=770><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" span=7 width=98><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=20 width=84>Box Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 width=98>Run Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=98></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=98></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=98></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=98></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=98></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=98></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>101012190 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01D2010066</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01C2010075 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01A2010069</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01B2010060 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01C2010075</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01D2010066 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">LM01C2010075</TD></TR></TBODY></TABLE>

 

[xenou]

Hmmm,
Box Number Run Number
101012190 LM01D2010066
......... LM01C2010075
......... LM01A2010069
......... LM01B2010060
......... LM01C2010075
......... LM01D2010066
......... LM01C2010075

That's two columns of output, not one. What's the deal here. Are there nulls in your recordset? Are the empty values blanks or are the supposed to be with the value above? I don't understand how you have two columns in your result set but only query for one field:

SELECT [RunNumber] FROM [PFDBMirror].[Abound].[Box] WITH(NOLOCK) INNER JOIN [dbo].[UnitDisposition] WITH(NOLOCK) ON [UnitDisposition].[BoxID] = [Box].[BoxNumber] Where [Box].[BoxNumber]='" & Box_Number.Value & "';"

 

[pjleitner]

I only query and bring in one column; the Run Number. The query brings in all run numbers that are associated with the Box Number which is already in the Excel sheet. I hope that makes sense. Sorry for the confusion.

 

[xenou]

Okay, I switched the arguments in Resize() and removed application.transpose, and it seems to give me a row of data now. Sorry - I put it in a column too. Habit.

Assume a is dimmed as a variant:
Dim a As Variant

If Not rs.EOF Then
    a = rs.GetRows
    Sheet1.Cells(1, 1).Resize(UBound(a, 1) + 1, UBound(a, 2) + 1).Value = a
End If

 

[pjleitner]

Thanks. I'm still unclear on how and where to apply this in my code.

Public Sub DataExtractRunNumber()
 
    'Extract the Run Number
    Dim Box_Number       As Range, _
        Box_NumberList   As Range, _
        LastRow          As Long, _
        cnUnits          As ADODB.Connection, _
        strConn          As String, _
        rsUnits          As ADODB.Recordset
 
 
    LastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "DATA SOURCE=LMSRVDWP01;INITIAL CATALOG=PFDBMirror;"
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
 
    Set Box_NumberList = Worksheets("Data").Range("A2:A" & LastRow)
    Set cnUnits = New ADODB.Connection
    Set rsUnits = New ADODB.Recordset
 
    cnUnits.Open strConn
 
    For Each Box_Number In Box_NumberList
        With rsUnits
            ' Assign the Connection object
            .ActiveConnection = cnUnits
            ' Extract the Run Number
            .Open "SELECT [RunNumber] FROM [PFDBMirror].[Abound].[Box] WITH(NOLOCK) INNER JOIN [dbo].[UnitDisposition] WITH(NOLOCK) ON [UnitDisposition].[BoxID] = [Box].[BoxNumber] Where [Box].[BoxNumber]='" & Box_Number.Value & "';"
            ' Copy the Run Number into cells B:2 on Sheet2
            Sheet2.Range("B" & Box_Number.Row).CopyFromRecordset rsUnits
            .Close
        End With
 
 
 
 
 
    Next Box_Number
    cnUnits.Close
    Set rsUnits = Nothing
    Set cnUnits = Nothing
 
 
End Sub

 

[xenou]

I think like so:

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Box_Number [COLOR="Navy"]In[/COLOR] Box_NumberList
    [COLOR="Navy"]With[/COLOR] rsUnits
        .ActiveConnection = cnUnits
        .Open "SELECT [RunNumber] FROM [PFDBMirror].[Abound].[Box] WITH(NOLOCK) INNER JOIN [dbo].[UnitDisposition] WITH(NOLOCK) ON [UnitDisposition].[BoxID] = [Box].[BoxNumber] Where [Box].[BoxNumber]='" & Box_Number.Value & "';"
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] .EOF [COLOR="Navy"]Then[/COLOR]
            a = .GetRows
            Sheet2.Range("B" & Box_Number.Row).Resize(UBound(a, 1) + 1, UBound(a, 2) + 1).Value = a
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        .Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]Next[/COLOR] Box_Number

Remember to add this new variable to the procedure:
Dim a As Variant