Field cannot be updated

[gnod]

hi,

in the ListSheet, when i click the "update general services", it prompt an error - "Field cannot be updated"..



thanks..

[Charlize]

It's because you're using a formula to calculate the number in column AK of DBInfo on Annual_DBInfo sheet.

="Rate + " & Annual_InflationRate_GeneralServices*100 & "% Inflation Rate"

If you remove the formula from the destination sheet, it works.

[gnod]

yes, it works if the destination sheet doesn't contain a formula but is there any other solution because the destination sheet is already deployed to the user and my Modifier file is only workaround for their Mid-Year budget..

thanks..

[Charlize]

Modified version of this routine. Skip the column with the formula in it.[VBA]Sub Write2ClosedBookMultipleCellRange(WorkbookFullName As String, _
SQL As String, NewValue As Variant)
Dim conn As New Connection, rs As New Recordset
Dim intRow As Integer, intCol As Integer
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WorkbookFullName & _
";Extended Properties=""Excel 8.0;HDR=NO;"""
rs.Open SQL, conn, 1, 3
For intRow = 1 To UBound(NewValue, 1)
For intCol = 1 To UBound(NewValue, 2)
'*** additional check on which column
'*** but I don't know if you use this routine for something else
If intCol <> 6 Then
rs.Fields(intCol - 1).Value = NewValue(intRow, intCol)
End If
Next intCol
rs.MoveNext
Next intRow
rs.MoveFirst
rs.Update
rs.Close
conn.Close
End Sub[/VBA]

[gnod]

Thanks for your reply.. but this Sub is general procedure if you notice i use it also in the "Update Janitorial Services"

you give me an idea.. i will create a condition if it is Janitorial or General Services.. thanks..

[gnod]

hi,
when i click the "Update Janitorial Services" in the ListSheet, the file in the destination sheet gets bigger.. how can i save the closed workbook?

thanks

[Charlize]

Try this [vba]Sub Write2ClosedBookMultipleCellRange(WorkbookFullName As String, SQL As String, NewValue As Variant)
Dim conn As New Connection, rs As New Recordset
Dim intRow As Integer, intCol As Integer
'declare a variable to be used as workbookhandle
Dim datawb As Workbook

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WorkbookFullName & _
";Extended Properties=""Excel 8.0;HDR=NO;"""
rs.Open SQL, conn, 1, 3

For intRow = 1 To UBound(NewValue, 1)
For intCol = 1 To UBound(NewValue, 2)
rs.Fields(intCol - 1).Value = NewValue(intRow, intCol)
Next intCol
rs.MoveNext
Next intRow
rs.MoveFirst
rs.Update
rs.Close
conn.Close
'open the closed workbook and save it again
'without updating the screen
Application.ScreenUpdating = False
'define the handle
Set datawb = Workbooks.Open(WorkbookFullName)
datawb.Save
datawb.Close
Application.ScreenUpdating = True
Set datawb = Nothing
End Sub[/vba]

[gnod]

is there any other way without opening the closed workbook?
i'm trying the
[VBA]
rs.Save WorkbookFullName
[/VBA]

after the rs.Update but it prompt me that the file is already exist
how can i select "Yes" in the prompt?


Thanks..