If the current code does everything you want except it puts the wrong month
name in the first row, the problem is he
If m = 1 Then msSheet.Cells(HeaderRow - 1, curcolumn).Value = MonthName(y)
I suggest you pass both m and j to your procedure
Public Function PopulatePage(x as Integer, Optional y As Integer = 1)
.. . .
If m = 1 Then msSheet.Cells(HeaderRow - 1, curcolumn).Value = MonthName(x)
.. . .
End Function
so you would call it with
Call PopulatePage(m, j)
--
Regards,
Tom Ogilvy
"poppy" wrote in message
...
Hi Tom
I just checked the data that is printed out and it turns out to be the
data I need, it is just that the heading is January to March instead of
July to September. This is what I did with my code:
Code:
--------------------
j = 0
For m = curMonth - 3 To curMonth - 1
j = j + 1
MerchantMonthly = "select branch, sum(qty), sum(b.vat)" & _
" from service_providers a left outer join cb b on a.sp_name = b.sp_name
and month" & _
" (inv_date) = " & m & " and year(inv_date) = " & curYear & " order by
a.sp_name"
If rs.State = -1 Then rs.Close
rs.Open MerchantMonthly, cn, adOpenKeyset, adLockOptimistic, adCmdText
'dump data into excel by calling function
Call PopulatePage(j)
.....
End sub
------------------------------------------------------------------------
----------------------
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 Function
--------------------
What do I do now?
--
poppy
------------------------------------------------------------------------
poppy's Profile:
http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=266048