P
poppy
Hello again
And no I'm not dumb, I'm just having one of those days.
This time I have an even bigger problem that I've been battling wit
for a while. The only way to really explain it is to upload an exce
file which I have done - virus free I promise.
This is the code I'm working with
Code
-------------------
cn.Open cString
For m = 1 To curMonth
ServBattbyDealer = "select a.sp_name as branch, a.mat_grp_desc as material_group, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat) as value_excl_vat " & _
" from services_by_dealer a left outer join cb b on a.sp_name = b.sp_name and a.mat_grp_desc = b.mat_grp_desc" & _
" and month(b.inv_date) = " & m & " and year(b.inv_date) = 2004 group by a.sp_name,a.mat_grp_desc order by a.sp_name, a.mat_grp_desc"
If rs.State = -1 Then rs.Close
rs.Open ServBattbyDealer, cn, adOpenKeyset, adLockOptimistic, adCmdText
'dump data into excel
Call PopulatePage2(m)
rs.Close ' Close the recordset for next loop
Next m
-------------------
Code
-------------------
Public Function PopulatePage2(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 + 1).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
-------------------
The problem as you can see from my file is that some columns are bein
repeated which I dont want.
If you dont want to look at the file this is my problem in it'
simplest form.
Data is being extracted from a sql database for each month using th
above query, the data is then populated into an excel file. Each mont
has Qty and Price
This is the output I'm getting:
Product Group Desc Qty Price Product Group Desc Qty Price Produc
Group Desc Qty Price Product Group Desc Qty Price
This is what I'm actually looking for:
Product Group Desc Qty Price Qty Price Qty Price Qty Price
I hope I have not confused you, because I'm begining to confus
myself.
I would really appreciate some help.
Kind Regard
And no I'm not dumb, I'm just having one of those days.
This time I have an even bigger problem that I've been battling wit
for a while. The only way to really explain it is to upload an exce
file which I have done - virus free I promise.
This is the code I'm working with
Code
-------------------
cn.Open cString
For m = 1 To curMonth
ServBattbyDealer = "select a.sp_name as branch, a.mat_grp_desc as material_group, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat) as value_excl_vat " & _
" from services_by_dealer a left outer join cb b on a.sp_name = b.sp_name and a.mat_grp_desc = b.mat_grp_desc" & _
" and month(b.inv_date) = " & m & " and year(b.inv_date) = 2004 group by a.sp_name,a.mat_grp_desc order by a.sp_name, a.mat_grp_desc"
If rs.State = -1 Then rs.Close
rs.Open ServBattbyDealer, cn, adOpenKeyset, adLockOptimistic, adCmdText
'dump data into excel
Call PopulatePage2(m)
rs.Close ' Close the recordset for next loop
Next m
-------------------
Code
-------------------
Public Function PopulatePage2(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 + 1).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
-------------------
The problem as you can see from my file is that some columns are bein
repeated which I dont want.
If you dont want to look at the file this is my problem in it'
simplest form.
Data is being extracted from a sql database for each month using th
above query, the data is then populated into an excel file. Each mont
has Qty and Price
This is the output I'm getting:
Product Group Desc Qty Price Product Group Desc Qty Price Produc
Group Desc Qty Price Product Group Desc Qty Price
This is what I'm actually looking for:
Product Group Desc Qty Price Qty Price Qty Price Qty Price
I hope I have not confused you, because I'm begining to confus
myself.
I would really appreciate some help.
Kind Regard