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

 

[Comfy]

I would do one of the following:

Delete the column once the RecordSet has been copied.
Loop through each Record in the RecordSet and only copy the required columns.

 

[fboehlandt]

Hi there

tha sounds great but how do I do that? The tricky part is I cannot copy it to Excel first due to locked cells (perhaps to another worksheet but that would be somewhat less ideal). Can the column be deleted from the rs object directly?

 

[Kyle123]

I seem to thing with the GetRows() function you can pass an array of field numbers to the last argument.

You can then write this to the sheet without looping

 

[fboehlandt]

Hm...not sure what GetRows does. This is what I have so far:

Set conn = CreateObject("ADODB.Connection")
conn.Open sConnString
Set rs = conn.Execute(CmdStrExec)varValues = rs.GetRows(2)
intFieldCount = UBound(varValues, 1)
intRowCount = UBound(varValues, 2)
ReDim MyArray(0, 0)
'Check Data
For i = 0 To intRowCount
 For j = 1 To intFieldCount
  ReDim Preserve MyArray(i, j - 1)
  '???
 Next j
Next i

Not really sure how to assign the recordset to the array?

 

[Comfy]

Have a look at this. There seems to be some useful info.

Retrieving Data From an ADO Recordset Using GetRows() - Microsoft Access / VBA

You'll want to list all the column headers you wish to retrive in an Array and then use this along with the RecordSet.RecordCount in the GetRows() Method.

 

[Kyle123]

GetRows simply returns the recordset in a 2dimensional array, so you can do something like:

MySheet.Range("A1:D50").Value = GetRows()

In the back of my mind, something is telling me you can also do:

MySheet.Range("A:D50").Value = GetRows(,,Array(2,3,4,5))

Where the numbers are the column numbers

 

[fboehlandt]

Thanks for the replies everyone

I'm not really getting anywhere with this, the problem is a little trickier than intially anticipated. The raw data is a normalised table with:

[Account] [SrcUD2] [Amount]
col1 row1 1000
col1 row2 500
col1 row3 500
col1 row4 1000
col1 row5 1000
col1 row6 0
col1 row7 1000
col1 row8 1000
col1 row9 0
col2 row1 100
col2 row2 1000
col2 row3 200

The result of the query is a pivoted 'table' that looks like the table below. The tricky part is that column 1 is regarded as data but should not be copied when transfering to the worksheet. The column headings do not represent fields as such which might be the reason that the GetRows method does not work. I also tried

rs.GetRows(rs.RecordCount, , "Amount")

but no joy ('Operation is not allowed in this context'). It feels that I almost have the data in the format I need it but cannot copy the number values only.

SrcUD1	col1	col2	col3	col4	col5	col6	col7
row1	1000	100	...
row2	500	...
row3	...
row4
row5
row6
row7
row8
row9

<tbody>
</tbody>

 

[Kyle123]

Couple of alternatives:

Delete the column after it's been imported
Do the Pivot in Excel

 

[RoryA]

Did you try using an array of field numbers as Kyle demonstrated?