Update Excel Spreadsheet via ADO

[CreganTur]

I'm working through an Access book, and the current section I'm in deals with DAO and ADO methods.

The following method uses ADO to connect to an Excel Spreadsheet, and is supposed to allow me to update the spreadsheet. However, whenever I run this code, I get
Run-time error '-2147217911 (80040e09)': Cannot Update. Database or object is read-only.

Now, I don't have any protection on the spreadsheet, so I don't understand why this isn't working. What can I do to allow the spreadsheet to be updateable? I've included the spreadsheet below.

Code (I run the code via the Immediate Window):
Sub OpenExcelADO(strFileName As String)
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strFindWhat As String
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\Acc07_ByExample" & _
"\" & strFileName & _
";Extended Properties=""Excel 8.0; HDR=Yes;"";"

Set rst = New ADODB.Recordset
rst.Open "Select * From [Sheet1$]", conn, adOpenStatic, adLockOptimistic
strFindWhat = "[Excel Version] = 'Excel 2000'"
rst.Find strFindWhat
rst(1).Value = "500"
rst.Update
rst.Close
Set rst = Nothing
MsgBox "Excel Spreadsheet was opened and updated."
conn.Close
Set conn = Nothing
End Sub

I can see a lot of use for this method in some projects I'm working on, but if I can't get the example to work, then I'm not going to waste time on adding it into a real project.

TIA:thumb

[Tommy]

FWIW I had no problems with the code as posted.
NOTE: I was using Word I don't have Access.

[CreganTur]

FWIW I had no problems with the code as posted.
NOTE: I was using Word I don't have Access.

Grrr...:mad2: I'm glad that you were able to get it to work...means the code's solid. I just can't understand why I'm locked out of being able to update the Spreadsheet.

This is the line that gets highlighted during the error:
rst(1).Value = "500"

[Tommy]

Yes I changed that particular line to something like 8000 and the code changed it to 500.

Are you using any other code to run this? Is the cusor set to readonly?

I have the reference "Microsoft Acivex Data Objects 2.8 Library" checked.

I just reran just in case I was holding my mouth just right and it worked again. :)

[Tommy]

I also tried to run it with the spreadsheet open and it still worked!! :)

[CreganTur]

I have the same reference checked.

It seems like the problem might be with the Excel Spreadsheet... but I'm not sure.

I appreiacte your Help Tommy, but I also want to see if anyone running Access has the same issue.

[Ken Puls]

Hi Randy,

I tried it with Access 2007 and Excel 2007. No issues here either.

[CreganTur]

Hi Randy,

I tried it with Access 2007 and Excel 2007. No issues here either.

I'm running Access and Excel 2003.

What could be causing the spreadsheet to be non-updateable on my machine? We've proven that the code's solid and that the spreadsheet is good, but for some reason 2 good parts won't add up to a good whole on my comp.

I even deleted the spreadsheet from my computer and downloaded the attachment from my first post, and it still won't work.

I'm completely at a loss as to why this isn't working for me :think:

[stanl]

I just tried it; no problems. Most of the time thatr particular error is associated with permissions problems and it looks as though there is no error in the code or workbook, perhaps you need to elucidate more on your environment. Stan

[CreganTur]

perhaps you need to elucidate more on your environment.

I'll give you what I can...

I'm on a work machine- Windows XP SP 2. Microsoft Office 2003 edition.
I've got admin rights to my machine
Security for the Report.xls spreadsheet shows all permissions (except special permissions) and nothing denied.
I can open, edit, save, do anything to the spreadsheet itself that I want to- there's no protection on it at all.

Not really sure what else to look for :dunno

[stanl]

I'll give you what I can...

I'm on a work machine- Windows XP SP 2. Microsoft Office 2003 edition.
I've got admin rights to my machine
Security for the Report.xls spreadsheet shows all permissions (except special permissions) and nothing denied.
I can open, edit, save, do anything to the spreadsheet itself that I want to- there's no protection on it at all.

Not really sure what else to look for :dunno

I wouldn't either, if I was in your shoes. Time for 'last resort' thinking. I ran the code and it updated fine, only difference was I didn't pass the file as a parameter, I hard-coded it.


Sub OpenExcelADO()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strFindWhat As String
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\temp\report.xls;Extended Properties=""Excel 8.0; HDR=Yes;"";"

Set rst = New ADODB.Recordset
rst.Open "Select * From [Sheet1$]", conn, adOpenStatic, adLockOptimistic
strFindWhat = "[Excel Version] = 'Excel 2000'"
rst.Find strFindWhat
rst(1).Value = "500"
rst.Update
rst.Close
Set rst = Nothing
MsgBox "Excel Spreadsheet was opened and updated."
conn.Close
Set conn = Nothing
End Sub


...like I said, last resort, but maybe you could try the sub as hard-coded then we could look at how it was passed. .02 Stan

[CreganTur]

I hard-coded the spreadsheet's filepath into the connection string, but it still gave me the same error in Access.

Below is the full connection string that's being used.


Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\Acc07_ByExample\Report.xls;Mode=Share Deny None;Extended Properties="Excel 8.0; HDR=Yes;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False



I did just try this in Word VBA, and it worked perfectly. I guess this means there's something wrong going on between Access and Excel. I don't know what it could be though. I was starting to think it might be something to do with the way my employer has things setup, but if it works in Word, then maybe that's not the case.

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: stupid smileys:bat2: *glares at quote text*

[Ken Puls]

So if it works from Word, do you have any other Office apps you can try it from? (Publisher, Powerpoint, Outlook?)

If it works from all of them, then the issue would point back to something in the Access installation...

[stanl]

you might try trimming the connection string; most of it is not needed. There is possibly an issue with

Jet OLEDB:Engine Type=35

maybe should be 40? Stan