CopyFromRecordset method
 

Hi All

In one of the posts that I read here the suggestion was made to me tha
I use the CopyFromRecordset method. I have tried to use this method an
it is way faster than what I'm doing at the moment, however I'
experiencing some difficulties. This is the piece of code I was workin
with which works fine, but is veerrryy slow:

Code
-------------------
Public Function PopulatePage(Optional y As Integer = 1)
Dim RowCount, headerrow, curcolumn, m

headerrow = 2
RowCount = headerrow
While rs.EOF = False
RowCount = RowCount + 1

For m = 0 To rs.Fields.Count - 1
curcolumn = IIf(m = 0, 1, ((y - 1) * (rs.Fields.Count - 1)) + m + 1)

If m = 1 Then msSheet.Cells(headerrow - 1, curcolumn).Value = MonthName(y) _

msSheet.Cells(headerrow - 1, curcolumn).Font.Color = vbYellow
msSheet.Cells(headerrow - 1, curcolumn).Font.Size = 12
msSheet.Cells(headerrow - 1, curcolumn).Font.Bold = True
msSheet.Cells(headerrow - 1, curcolumn).Interior.Color = vbBlue

msSheet.Cells(headerrow, curcolumn).Value = ProperCase(Replace(rs.Fields(m).Name, "_", " "))
msSheet.Cells(headerrow, curcolumn).Font.Color = vbYellow
msSheet.Cells(headerrow, curcolumn).Font.Size = 12
msSheet.Cells(headerrow, curcolumn).Font.Bold = True
msSheet.Cells(headerrow, curcolumn).Interior.Color = vbBlue
msSheet.Cells(RowCount, curcolumn).Value = rs.Fields(m).Value
Next m
rs.MoveNext
Wend

End Functio
-------------------

This then is the piece of code I'm working on at the moment and it'
giving me some problems.


Code
-------------------
cn.Open cString
For m = 1 To curMonth - 1
ServBattbyGroup = "select mat_grp_desc as material_group,sum(qty) as qty, sum(value_incl_vat - vat) as value_excl_vat from cb where mat_group in"
ServBattbyGroup = ServBattbyGroup + " ('CBS02', 'CBS60', 'CBS97', 'CBS98', 'CBS99') and mat_group is not null and mat_group < ''"
ServBattbyGroup = ServBattbyGroup + " and month(inv_date) = " & m & " and year(inv_date) = 2004 and upper(hstatus) < 'D' group by mat_grp_desc"

If rs.State = -1 Then rs.Close

rs.Open ServBattbyGroup, cn, adOpenKeyset, adLockOptimistic, adCmdText
Dim lastcol, col, row, headerrow

lastcol = Cells(3, Columns.Count).End(xlToLeft).Column
row = 2
col = lastcol
Do While Not rs.EOF
'dump data into excel
For col = 0 To rs.Fields.Count - 1
col = lastcol + 1

msSheet.Cells(row, col).Value = ProperCase(Replace(rs.Fields(col).Name, "_", " "))
msSheet.Cells(row, col).CopyFromRecordset rs
Next col
lastcol = Cells(3, Columns.Count).End(xlToLeft).Column
' col = col + 1
Loop '
rs.Close ' Close the recordset for next loop

Next
-------------------

One of the problems is that the column headings are not being displaye
and the first column is contantly being skipped. Where am I goin
wrong?

Is it possible for me to do what I am doing in the second piece of cod
what I'm doing in the first? :confused: That is incorporate th
CopyFromRecordset method into my first code. Could you include comment
in your help please if possible.

Your input would be highly appreciated. :(

Kind Regard

--
Message posted from http://www.ExcelForum.com