初次接触关于Excel的问题
将Excel中的数据导入Access中时碰到如下的问题第一个问题:
使用代码:
Dim sql As String, rs As Recordset
Dim adoCon As New ADODB.Connection, str1 As String
Dim InputFileName As String
InputFileName = "e:\Work-Temp\DB-Edit1.xls"
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & InputFileName & ";Extended Properties=Excel 8.0;Persist Security Info=true"
adoCon.Open str1
sql = "select * from [sheet1$]"
Set rs = adoCon.Execute(sql)
Set DataGrid1.DataSource = rs
结果在最后一行出现“行集合不能作为标签”的错误,该如何解决第二个问题
Dim sql As String, rs As Recordset
Dim adoCon As New ADODB.Connection, str1 As String
Dim InputFileName As String
InputFileName = "e:\Work-Temp\DB-Edit1.xls"
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & InputFileName & ";Extended Properties=Excel 8.0;Persist Security Info=true"
adoCon.Open str1
sql = "select * from [sheet1$]"
rs.Open sql, adoCon, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = rs代码与第一个问题的基本一致,不同的地方在倒数第二行,执行倒数第二行时出现了“对象变量或With块变量未设置”的错误,问题出现在什么地方,该如何解决?Set rs = adoCon.Execute(sql)
rs.Open sql, adoCon, adOpenKeyset, adLockOptimistic
上面这两句的区别与联系
将Excel中的数据导入Access中时碰到如下的问题第一个问题:
使用代码:
Dim sql As String, rs As Recordset
Dim adoCon As New ADODB.Connection, str1 As String
Dim InputFileName As String
InputFileName = "e:\Work-Temp\DB-Edit1.xls"
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & InputFileName & ";Extended Properties=Excel 8.0;Persist Security Info=true"
adoCon.Open str1
sql = "select * from [sheet1$]"
Set rs = adoCon.Execute(sql)
Set DataGrid1.DataSource = rs
结果在最后一行出现“行集合不能作为标签”的错误,该如何解决第二个问题
Dim sql As String, rs As Recordset
Dim adoCon As New ADODB.Connection, str1 As String
Dim InputFileName As String
InputFileName = "e:\Work-Temp\DB-Edit1.xls"
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & InputFileName & ";Extended Properties=Excel 8.0;Persist Security Info=true"
adoCon.Open str1
sql = "select * from [sheet1$]"
rs.Open sql, adoCon, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = rs代码与第一个问题的基本一致,不同的地方在倒数第二行,执行倒数第二行时出现了“对象变量或With块变量未设置”的错误,问题出现在什么地方,该如何解决?Set rs = adoCon.Execute(sql)
rs.Open sql, adoCon, adOpenKeyset, adLockOptimistic
上面这两句的区别与联系
Dim excel_sheet As Object
Dim db As Database
Dim AccessPath As String, AccessTable As String
Dim sql As String
Dim frm As New frmMessage
Dim msg As String
ADOsdb.BeginTrans
' With ADOsdb
' If .State <> adStateOpen Then
' .CursorLocation = adUseClient
' .ConnectionString = gsOdbcName
' .Open
' End If
' End With
With dlgCommonDialog
.DialogTitle = "打开"
.CancelError = False
'ToDo: 设置 common dialog 控件的标志和属性
.Filter = "Excel文件 (*.xls) ¦*.xls"
.ShowOpen
If Len(.FileName) = 0 Then
Exit Sub
End If
sfile = .FileName
End With
AccessPath = lcspath & "\db.mdb" '数据库路径
excelpath = sfile '电子表格路经
AccessTable = "db" '数据库内表格
msg = Trim(InputBox("请输入表名,如sheet1或sheet2:", "工作表", "sheet1"))
' msg = Str(msg)
sheet = msg '电子表格内工作表
Set db = OpenDatabase(excelpath, True, False, "Excel 8.0") '打开电子表格文件
sql = ("Select * into [;database=" & AccessPath & "]." & AccessTable & " FROM [" & sheet & "$]")
' sql = ("Select * into [;database=" & AccessPath & "]." & AccessTable & " FROM [" & sheet & "]")
If sheet = "" Then
MsgBox "您选择的EXCEL表不存在,请重新导入!", vbInformation, "抱歉!"
Exit Sub
End If
If deltable = 1 Then
With ADOsdb
.Execute "drop table db", , adCmdText
End With
End If
ADOsdb.CommitTrans db.Execute (sql) '将电子表格导入数据库
ShowMessage "正在导入EXCEL表,请您稍等..."
Timer1.Enabled = True
这些代码你真的测试过吗?
从你给的代码中 lcspath 没有赋值,ShowMessage 这个还函数也没有(不过不影响,可以删除)
如果你真的测试过,那就是还有很多代码没有给出
第二个问题:rs 对象未创建,当然不能调用 Open 方法了。
第二个问题已经解决但是第一个问题,你提到Excel格式不对,做何解呢,Excel中的数据为如下格式:
股票代号 股票名称 收盘价 涨跌 最高价 最低价 成交量
0500001 基金金泰 126.5 -7.5 132 125 6290
0500002 基金泰和 85 -3 85.5 83 6153
0500003 基金安信 81 -3 82.5 79.5 2090
0500005 基金汉盛 77 -0.5 77.5 75 1273
表名前加“[”,表名后加“&]”
表名确实需要加入[],我的代码中也是有的to Tiger_Zhao:
我现在确实选用了第二种方案,但是还是不能和表格建立绑定,出现的错误和第一种方案一样。
但是我找到了一个用Adodc控件的例子,却可以绑定,没有任何代码,只是在属性里设置了以下的属性
其链接字符串为:Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="Driver={Microsoft Excel Driver (*.xls)};DBQ=71.XLS"
sql命令为:Select * From [股市行情表$]