CopyFromRecordset: skip first column

[fboehlandt]

Hi everyone

I am using the CopyFromRecordset method to transfer data from Access to an Excel worksheet. The query (CmdStrExec) yields a table with eight columns. However, I would like to transfer columns 2 to 8 only. I have the code below to transfer all data:

Set conn = CreateObject("ADODB.Connection")
conn.Open sConnString
Set rs = conn.Execute(CmdStrExec)
    
'Check Data
If Not rs.EOF Then[INDENT]'Transfer result to Workbook[/INDENT]
[INDENT]TargetCell.CopyFromRecordset rs
'Close the recordset[/INDENT]
[INDENT]rs.Close[/INDENT]
[INDENT]ConnectSqlServer = "Import complete"[/INDENT]
Else[INDENT]rs.Close
ConnectSqlServer = "No records returned"[/INDENT]
End If

Unfortunately, I cannot easily remove said column when running the query. The source data is normalised and is pivoted using said column as first column before export. For completeness sake I include the SQL query as well.

CmdStrExec:

TRANSFORM First(Amount)
SELECT SrcUD2
FROM source
WHERE LocName="myLoc" AND Entity="LE01" AND PeriodName="QA - 2014" AND ScenarioName="Actual" 
AND (Account="col1" Or Account="col2" Or Account="col3" Or Account="col4" Or Account="col5" Or Account="col6" Or Account="col7")
AND (SrcUD2="row1" Or SrcUD2="row2" Or SrcUD2="row3" Or SrcUD2="row4" Or SrcUD2="row5" Or SrcUD2="row6" Or SrcUD2="row7" Or SrcUD2="row8" Or SrcUD2="row9")
GROUP BY SrcUD2
PIVOT Account

Any help is greatly appreciated

 

[Kyle123]

Quick Demo:

    Dim rs          As Recordset
    Dim Data()      As Variant
    
    With New Connection
        .ConnectionString = "YourConnectionString"
        .Open
        Set rs = .Execute("SELECT top 5 Field0, Field1, Field2 FROM SomeTable")
    End With
    
    Data = Application.Transpose(rs.GetRows(, , Array(1, 2)))
    
    Sheets(1).Cells(1).Resize(UBound(Data), UBound(Data, 2)).Value = Data

 

[fboehlandt]

@Rory

I tried two things:

        Dim avarFieldNames(1 To 3) As Variant
        avarFieldNames(1) = 1
        avarFieldNames(2) = 2
        avarFieldNames(3) = 3
        MyArray = rs.GetRows(rs.RecordCount, , avarFieldNames)

which gives me a type mismatch error. Alternatively

MyArray = rs.GetRows(rs.RecordCount, , "col1")

which gives me 'Either BOF or EOF is Truem or the current record has been deleted.'

Any ideas?

 

[Kyle123]

Did you read my last post where I gave you an example?

it's tested and works

 

[fboehlandt]

@Kyle

I think this might work if I had to transpose the data. However, the query that runs pivots the normalised data so that it sits in a meaningful matrix format. I don't see how I could achieve that without having to dump the data to a physical pivottable?

 

[RoryA]

The Transpose in Kyle's code is only there because GetRows returns an array that is transposed from the way you would expect.

If you're getting an EOF or BOF error, there is no data in your recordset, or you've moved to the end of it.

 

[fboehlandt]

@Kyle:

sorry, my bad. I think I know what you mean. I tried for the two-col array and it seems to work Let me test the full dataset and I will report back

 

[Kyle123]

Glad it's helped

Thanks Rory

 

[fboehlandt]

Done! Great stuff, guys. Last question: is there a way to determine the last column in said table to I can autoassign the array, something on the lines of:

lastcol (should be seven)

For i = 1 to lastcol
MyArray(i) = i
Next

Dim Data() As Variant
Data = Application.Transpose(rs.GetRows(, , MyArray))

 

[RoryA]

rs.Fields.Count

will give the field count.

 

[fboehlandt]

stands to reason. Thanks again!!