poppy Wrote:
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 bein
displayed and the first column is contantly being skipped. Where a
I going wrong?

Is it possible for me to do what I am doing in the second piece o
code what I'm doing in the first? That is incorporate th
CopyFromRecordset method into my first code. Could you includ
comments in your help please if possible.

Your input would be highly appreciated. :(

Kind Regards

Could you please help me here cause I still need help

--
popp
-----------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...fo&userid=1145
View this thread: http://www.excelforum.com/showthread.php?threadid=25780