Solved: Update an excel sheet, by loading from an object

[snkartha]

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

[snkartha]

Next Question

How can I use the Index Function in vba. When I am trying to use the following function in the vba program like

Application.WorksheetFunction.Index(A2:V4,MATCH("Jagan",C2:C4,0),5)

I get an error like "Expected; List Separator or )" and it flashes in the : between A2:V4. If I use the same Index command in an excel sheet it works fine.

[snkartha]

The answer is very simple. As we update a table using sql query, it is possible to update the excel sheet as well. I was not sure if this was possible.

You can query the sheet and pass commands in vba code like

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