PDA

View Full Version : Cannot update the fields using ADODB from VBA



SotM
2017-03-15, 09:28 PM
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: 6581
I tried several approaches:




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.

strFieldNames = "[Proj_Year]" I receive only one column but I met same restrictions as in 1.

strFieldNames = "CInt([Proj_Year]) as [Proj_Year]" I receive the required columns in type I want. But when I ran the code I receive:

Field cannot be updated

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?