Cannot update the fields using ADODB from VBA

[SotMrus]

My goal is to be able to read and write the cell values from a closed workbook. I use ADODB to get the needed information. A file is generated by a corporate website, so I can't change the contents before using the actual file. The file is in Excel format. No formulas in cells present, just values. I would like to get dates, strings, integers from a worksheet, but I met some restrictions.

I wrote the sample code to show you what happens:

Dim rsConn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim strFileName As String
Dim strFieldNames As String
Dim intValue As Integer

strFileName = "C:\Tests\Sample.xlsx" ' Fullpath to a workbook
'strFieldNames = "CInt([Proj_Year]) as [Proj_Year]"
'strFieldNames = "[Proj_Year]"
strFieldNames = "*"

Set rsConn = New ADODB.Connection
With rsConn
    .ConnectionString = "Data Source=" & strFileName & "; Extended Properties=""Excel 12.0; HDR=YES; "";"
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open
End With

Set rsData = New ADODB.Recordset
With rsData
    .Source = "SELECT " & strFieldNames & " FROM A2:AE500;" ' Sheetname not required
    .ActiveConnection = rsConn
    '.CursorType = adOpenKeyset     ' Tried this - didn't work
    '.CursorType = adOpenDynamic     ' Tried this - didn't work
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open
End With

With rsData
    .MoveFirst
    Do While Not .EOF
        ' Getting the value
        intValue = .Fields(0).Value

        ' Make some crazy modifications of the value
        intValue = intValue + 10

        ' Updating
        .Fields(0).Value = intValue   ' this is place where crash happens

        ' Move to the next record
        .MoveNext
    Loop
End With

rsData.Close
Set rsData = Nothing
rsConn.Close
Set rsConn = Nothing

Here is a screenshot of the sample workbook: http://i.imgur.com/gRRUs5q.jpg

I tried several approaches:

1. strFieldNames = "*"

   I get all columns present in the worksheet. But the driver tries to guess the field types and I don't like it because its guessing are wrong. For example, cell I3 should be string but in reality it is adDouble. So the reading might be ok but I can't write back as a string. I tried using IMEX=1 but it didn't help, tried MAXSCANROWS=1, Readonly=0 but also have no luck. I don't want to touch windows registry to modify guessing rows.

2. strFieldNames = "[Proj_Year]"

   I receive only one column but I met same restrictions as in 1.

3. strFieldNames = "CInt([Proj_Year]) as  [Proj_Year]"

   I receive the required columns in type I want. But when I ran the code I receive:
   
   [HIGHLIGHT]Field cannot be updated[/HIGHLIGHT]
   
   in code: .Fields(0).Value = intValue

In the code above I read and write by one record at a time. I tried to read all data (30 columns, 3000 rows) by using GetRows and then parse the array of data into my own class. After all data is read then I modify this data and write it back to the worksheet one record at a time.

What do I need to do in order to get this code working?

 

[John_w]

Please post links to your request on other forums (cross-posting etiquette).

 

[SotMrus]

Ok, here are the links to the same question on other forums, maybe later it will be more:
1. Cannot update the fields using ADODB from VBA
2. excel - Cannot update the fields using ADODB from VBA - Stack Overflow
3. MsOf13 Cannot update the fields using ADODB from VBA-VBForums
4. https://www.excelforum.com/excel-pr...t-update-the-fields-using-adodb-from-vba.html
5. Cannot update the fields using ADODB from VBA