Cannot update the fields using ADODB from VBA

SotM

New member
Joined
Mar 15, 2017
Messages
1
Reaction score
0
Points
0
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:

Code:
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: shot.jpg
I tried several approaches:


  1. Code:
    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. Code:
    strFieldNames = "[Proj_Year]"
    I receive only one column but I met same restrictions as in 1.
  3. Code:
    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?
 
Back
Top