P
poppy
Hi Tom
I just checked the data that is printed out and it turns out to be th
data I need, it is just that the heading is January to March instead o
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 Functio
I just checked the data that is printed out and it turns out to be th
data I need, it is just that the heading is January to March instead o
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 Functio