求 用db连接excel的连接语句

[lddp034]

conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cFileName & ";Extended Properties=""Excel 11.0;HDR=Yes;IMEX=1"""
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cFileName & ";Extended Properties='"Excel 11.0;HDR=Yes;IMEX=1"'"
上面这两个语句我都试过了 ，还是不行，请老师指教，谢谢！

[华芸智森]

Microsoft Excel
Driver={Microsoft Excel Driver (*.xls)};
DBQ=physical path to .xls file;
DriverID=278

Microsoft Excel 97 Driver={Microsoft Excel Driver (*.xls)};
DBQ=physical path to .xls file;
DriverID=790

=================

Public DbStyle As String

'/数据库类型.
Public Enum SmDbType
Access = 1 'Access
Excel = 2 'Excel
Text = 3 '文本
FoxPro = 4 'FoxPro
dBase = 5 'dBase
End Enum

'
'创建一个连接(连接到其它数据库类型)
'函数名:CreateOtherConn
'参数: DbConnection ADODB连接,FilePath 数据库路径,UserName 登录用户名,PassWord 登录密码,DbType SmDbType枚举数据库类型
'返回值:TRUE 连接成功.FALSE 连接失败.
'例:
'CreateOtherConn Cnn, "E:\CjhLx\dbf", , , FoxPro
'StrSql = "select * from [employee.dbf]"
'Set Rs = RsOpen(Cnn, StrSql)
'Set DataGrid1.DataSource = Rs

Public Function CreateOtherConn(ByRef DbConnection As ADODB.Connection, _
FilePath As String, _
Optional UserName As String = "admin", _
Optional PassWord As String = "", _
Optional DbType As SmDbType = Access) As Boolean
Dim ConnStr As String
Dim DriveName(5) As String
Dim tDbType(5) As String
Dim UserPwd(5) As String

'/驱动程序
DriveName(1) = "{Microsoft Access Driver (*.mdb)}"
DriveName(2) = "{Microsoft Excel Driver (*.xls)}"
DriveName(3) = "{Microsoft Text Driver (*.txt; *.csv)}"
DriveName(4) = "{Microsoft Visual FoxPro Driver};SourceType=DBF"
DriveName(5) = "{Microsoft dBase Driver (*.dbf)}"
'/类型
tDbType(1) = "MDB"
tDbType(2) = "XLS"
tDbType(3) = "TXT"
tDbType(4) = "FDB"
tDbType(5) = "DDB"
'/用户名和密码.
UserPwd(1) = "Uid=" & UserName & ";Pwd=" & PassWord & ";"
UserPwd(2) = "Uid=" & UserName & ";Pwd=" & PassWord & ";"
UserPwd(3) = ""
UserPwd(4) = "Uid=" & UserName & ";Pwd=" & PassWord & ";"
UserPwd(5) = "Uid=" & UserName & ";Pwd=" & PassWord & ";"

On Error Resume Next

If DbConnection.State = adStateOpen And Not IsEmpty(adStateOpen) Then
DbConnection.Close
End If
ConnStr = "Provider=MSDASQL.1;Persist Security Info=False;DRIVER=" & DriveName(DbType) & ";" & UserPwd(DbType) & "DBQ=" & FilePath
DbConnection.ConnectionString = ConnStr
DbConnection.Open
DoEvents

If Err.Number = 0 Then
DbStyle = tDbType(DbType)
CreateOtherConn = True
Else
Err.Clear
DbStyle = ""
CreateOtherConn = False
End If
End Function

[华芸智森]

Set Ex_Conn = CreateObject("adodb.connection")
Set schemars = CreateObject("adodb.recordset")
Ex_Conn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=d:\book1.XLS"
Set schemars = Ex_Conn.OpenSchema(ADODB.adSchemaTables)
Debug.Print schemars.RecordCount
Debug.Print schemars("table_name")
'====================================

adSchemaTables=20
set rs=conn.OpenSchema(adSchemaTables)
response.write rs.recordcount

[lcsfxs]

装ACCESS97试试

[lddp034]

谢谢mstop 的热情回复
不过
我用的是vb6.0,你发的好像是asp的？？？

[华芸智森]

不是,是VB6.0的.
你贴子相关的内容也可以在VB的FAQ中找到.

[of123]

conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

应该可以。你将赋值后的 conn1.ConnectionString 打印出来看看。
Debug.Print conn1.ConnectionString

[lddp034]

大哥真的不行啊，
我试过了

[lddp034]

阿？
好，我试试

[lddp034]

不管行不行了，我先散分！

[lddp034]

还没结贴，请高手们发表意见
能给我个例子吗？
谢谢各位了

[华芸智森]

1.可能你的EXCEL超出了.将 HDR=Yes 改为 HDR=False
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
这样,返回的就是 F1,F2,F3,F4,F5,....
2.可用改用行成表名方式.
FLDLIST="F1,F2,F3,F4,F5,F6,...F100"
不过,这样会有空列出现.
3.还是你的EXCEL有问题.第一行有问题,你将一个表中的有数据的部分COPY到一个新的EXCEL中.别存为一个文件,再用这方法试试.应该行得通.

[lddp034]

现在还是没解决，郁闷阿