Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CopyFromRecordset does nothing | Excel Programming | |||
ADO & CopyFromRecordset performance | Excel Programming | |||
CopyFromRecordset Bug with Office XP | Excel Programming | |||
CopyFromRecordset does nothing | Excel Programming | |||
Copyfromrecordset Bug ? | Excel Programming |