View Full Version : Field cannot be updated
hi,
in the ListSheet, when i click the "update general services", it prompt an error - "Field cannot be updated"..
:help
thanks..
Charlize
11-20-2007, 05:26 AM
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.
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
11-20-2007, 04:33 PM
Modified version of this routine. Skip the column with the formula in it.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
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..
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
11-22-2007, 03:16 AM
Try this 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
is there any other way without opening the closed workbook?
i'm trying the
rs.Save WorkbookFullName
after the rs.Update but it prompt me that the file is already exist
how can i select "Yes" in the prompt?
Thanks..
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.