VB中使用adodb更新excel中内容遇到的问题

[along7599]

Dim excelConn1 As New ADODB.Connection

excelConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=ReadWrite;Data Source=" & excelFile & ";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1;"""

excelConn.Open

Dim excelRec As ADODB.Recordset

excelRec.Open "select * from [01公共设施$]", excelConn, 1, 3

excelRec(21) = “new” 这里出错提示"不能更新。数据库或对象为只读。"

excelRec.Update

应该是哪里是只读的，但是我找不到在哪儿改，看了个帖子在连接字中加"ReadOnly=False;" 我加了就报错

[专注VB编程开发20年]

和你分享一下源码，有需要订做软件的话叫我

Private Sub Form_Load()
Dim RS As ADODB.Recordset
Set RS = GetExcelRs(App.Path & "\book1.xls")
MsgBox RS.RecordCount
MsgBox RS(0)
RS(0) = Timer
RS.Update
RS.Close
End Sub

'┏〓〓〓〓〓〓〓〓〓 GetExcelRs,start 〓〓〓〓〓〓〓〓〓┓
'［简介］：
'VB读取EXCEL工作薄某个表中数据
Function GetExcelRs(ByVal sFile As String, Optional ExcelSheetName As String = "sheet1", Optional ErrInfo As String) As ADODB.Recordset
   '[mycode_id:2025],edittime:2011-9-7 下午 02:15:41
   On Error GoTo Err
   Dim RS As ADODB.Recordset
   Set RS = New ADODB.Recordset
   Dim ConnStr As String
   ConnStr = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile & ";ReadOnly=False"
   
   RS.Open "SELECT * FROM [" & ExcelSheetName & "$]", ConnStr, 1, 3
   
   Set GetExcelRs = RS
   Set RS = Nothing
   
   Exit Function
   Err:
   ErrInfo = Err.Description
   MsgBox ErrInfo
End Function
'┗〓〓〓〓〓〓〓〓〓  GetExcelRs,end  〓〓〓〓〓〓〓〓〓┛

[专注VB编程开发20年]

如果你的资料有用，则不要直接用哦，上面的是即时更新的

Private Sub Form_Load()
Dim RS As ADODB.Recordset
Set RS = GetExcelRs(App.Path & "\book1.xls")
MsgBox RS.RecordCount
MsgBox RS(0)
RS(0) = Timer '更改内容
RS.CancelUpdate '取消更新
RS.Update '保存更新
RS.Close
End Sub

'┏〓〓〓〓〓〓〓〓〓 GetExcelRs,start 〓〓〓〓〓〓〓〓〓┓
'［简介］：
'VB读取EXCEL工作薄某个表中数据
Function GetExcelRs(ByVal sFile As String, Optional ExcelSheetName As String = "sheet1", Optional ErrInfo As String) As ADODB.Recordset
   '帮你写函数，帮你写代码，帮你写模块，帮你设计软件
   '--需要什么函数或功能，可以联系我。
   On Error GoTo Err
   Dim RS As ADODB.Recordset
   Set RS = New ADODB.Recordset
   Dim ConnStr As String
   ConnStr = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile & ";ReadOnly=False"
   
   RS.Open "SELECT * FROM [" & ExcelSheetName & "$]", ConnStr, 1, 3
   
   Set GetExcelRs = RS
   Set RS = Nothing
   
   Exit Function
   Err:
   ErrInfo = Err.Description
   MsgBox ErrInfo
End Function
'┗〓〓〓〓〓〓〓〓〓  GetExcelRs,end  〓〓〓〓〓〓〓〓〓┛

[along7599]

我的要兼容xlsx格式，用你的不行啊

[chinaboyzyq]

“new”

这个双引号不能用中文的吧？

[hwman]

IMEX=1
改为
IMEX=2就可以了

IMEX=0：写
IMEX=1：读
IMEX=2：读写

[vixiacai]

excel文件的属性是不是只读的？

[熊孩子开学喽]

用数据库方式操作excel, 个人不是很推荐, excel在数据类型方面存在很多不如意的地方, 容易出错.