snkartha
01-27-2010, 12:00 AM
Hi,
I am new to VBA. I am sort of getting familiar with vba coding.
I am attaching a file for easier understanding of my issue.
Please see the sheet "Summary_Sheet". The number of rows is just a sample. I will not know how many rows will be there in this excel sheet.
I have a collection object (retrieved from the "Data" excel sheet).
I am able to iterate through the "Data" sheet and get a collection object (Col_offshoreAssignees as shown in the code below. This is a separate class). Only hiccup here is I dont know how to update the "Summary_Sheet" using VBA.
Here is the code that I write to read from the "Summary_sheet". I am able to browse the contents and show them in MsgBox.
objRecordset1.Open "Select Month, Associate_Name, Client FROM [Summary_Sheet$A1:G4] where Client <> ' '", objConnection11, adOpenStatic, adLockOptimistic, adCmdText
rec = objRecordset1.RecordCount
MsgBox "Count the number of records " & rec
' following line contains the collection received from the "Data" sheet.
For Each anOffShAssignee In Col_offshoreAssignees
MsgBox "Inside Assignee Collection after setting the worksheet"
MsgBox anOffShAssignee.assigneeName
MsgBox anOffShAssignee.client
MsgBox anOffShAssignee.dataMonth
MsgBox anOffShAssignee.planHours
MsgBox anOffShAssignee.requestType
Next anOffShAssignee
'Followline are those from the "Summary_Sheet"
MsgBox "Browsing rows in the SummarySheet"
Do While objRecordset1.EOF = False
MsgBox "Offshore Assignee" & objRecordset1!Associate_Name
MsgBox "Client " & objRecordset1!client
MsgBox "Month " & objRecordset1!Month
objRecordset1.MoveNext
Loop
It is from here I dont know how to proceed to Update the "Summary_Sheet" and the columns that are shown in blue.
Bottomline, I will need to get an update command like in a loop
Update Summary_Sheet set Other=15 where Assignee_Name="jagan" and client="A" and Month=Oct
Update Summary_Sheet set Enhancement=64 where Assignee_Name="jagan" and client="B" and Month=Oct
Update Summary_Sheet set Defect=20 where Assignee_Name="ram" and client="A" and Month=Oct
If I can get the first step, I will have an idea as to how to proceed further.
Is this possible after all.
Can someone please help.
Thanks
I am new to VBA. I am sort of getting familiar with vba coding.
I am attaching a file for easier understanding of my issue.
Please see the sheet "Summary_Sheet". The number of rows is just a sample. I will not know how many rows will be there in this excel sheet.
I have a collection object (retrieved from the "Data" excel sheet).
I am able to iterate through the "Data" sheet and get a collection object (Col_offshoreAssignees as shown in the code below. This is a separate class). Only hiccup here is I dont know how to update the "Summary_Sheet" using VBA.
Here is the code that I write to read from the "Summary_sheet". I am able to browse the contents and show them in MsgBox.
objRecordset1.Open "Select Month, Associate_Name, Client FROM [Summary_Sheet$A1:G4] where Client <> ' '", objConnection11, adOpenStatic, adLockOptimistic, adCmdText
rec = objRecordset1.RecordCount
MsgBox "Count the number of records " & rec
' following line contains the collection received from the "Data" sheet.
For Each anOffShAssignee In Col_offshoreAssignees
MsgBox "Inside Assignee Collection after setting the worksheet"
MsgBox anOffShAssignee.assigneeName
MsgBox anOffShAssignee.client
MsgBox anOffShAssignee.dataMonth
MsgBox anOffShAssignee.planHours
MsgBox anOffShAssignee.requestType
Next anOffShAssignee
'Followline are those from the "Summary_Sheet"
MsgBox "Browsing rows in the SummarySheet"
Do While objRecordset1.EOF = False
MsgBox "Offshore Assignee" & objRecordset1!Associate_Name
MsgBox "Client " & objRecordset1!client
MsgBox "Month " & objRecordset1!Month
objRecordset1.MoveNext
Loop
It is from here I dont know how to proceed to Update the "Summary_Sheet" and the columns that are shown in blue.
Bottomline, I will need to get an update command like in a loop
Update Summary_Sheet set Other=15 where Assignee_Name="jagan" and client="A" and Month=Oct
Update Summary_Sheet set Enhancement=64 where Assignee_Name="jagan" and client="B" and Month=Oct
Update Summary_Sheet set Defect=20 where Assignee_Name="ram" and client="A" and Month=Oct
If I can get the first step, I will have an idea as to how to proceed further.
Is this possible after all.
Can someone please help.
Thanks