P
poppy
poppy said: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
-------------------
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
-------------------
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