Option ExplicitPublic Rs As New ADODB.Recordset Public Conn As New ADODB.Connection Dim ExcelApp As New Excel.ApplicationPrivate Sub Form_Load() Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source= " & App.Path & "\test.xls;Extended Properties='Excel 8.0;HDR=Yes'" Conn.CursorLocation = adUseClient Conn.Open Rs.Open "select * from [jobs]", Conn, adOpenKeyset, adLockOptimistic Set Me.DataGrid1.DataSource = RsEnd Sub
Dim sFile As String '文件地址 Dim oConn As New ADODB.Connection Dim intnum As Integer
With CommonDialog1 .DialogTitle = "打开" .CancelError = False .Filter = "所有文件 (*.XLS)|*.xls" .ShowOpen sFile = .FileName End With
With lvwlist .ColumnHeaders.Clear .ListItems.Clear .ColumnHeaders.Add , , , 0 For intnum = 0 To oRS.Fields.Count - 1 .ColumnHeaders.Add , , oRS.Fields(intnum).Name Next .View = lvwReport End With
oRS.MoveFirst With oRS While Not .EOF Set listitem = lvwlist.ListItems.Add(, , , 0) For intnum = 0 To oRS.Fields.Count - 1 If IsNull(oRS.Fields(intnum)) = True Then listitem.SubItems(intnum + 1) = "" Else listitem.SubItems(intnum + 1) = oRS.Fields(intnum) End If Next .MoveNext
Public Conn As New ADODB.Connection
Dim ExcelApp As New Excel.ApplicationPrivate Sub Form_Load()
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source= " & App.Path & "\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"
Conn.CursorLocation = adUseClient Conn.Open
Rs.Open "select * from [jobs]", Conn, adOpenKeyset, adLockOptimistic
Set Me.DataGrid1.DataSource = RsEnd Sub
Dim oConn As New ADODB.Connection
Dim intnum As Integer
With CommonDialog1
.DialogTitle = "打开"
.CancelError = False
.Filter = "所有文件 (*.XLS)|*.xls"
.ShowOpen
sFile = .FileName
End With
'设置导入的数据
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & sFile & "';Extended Properties=""Excel 8.0;"""
oRS.Open "Select * from [sheet1$]", oConn, adOpenStatic '打开Sheet1,也可以是定义的名称,或一个Range。
With lvwlist
.ColumnHeaders.Clear
.ListItems.Clear
.ColumnHeaders.Add , , , 0
For intnum = 0 To oRS.Fields.Count - 1
.ColumnHeaders.Add , , oRS.Fields(intnum).Name
Next
.View = lvwReport
End With
oRS.MoveFirst
With oRS
While Not .EOF
Set listitem = lvwlist.ListItems.Add(, , , 0)
For intnum = 0 To oRS.Fields.Count - 1
If IsNull(oRS.Fields(intnum)) = True Then
listitem.SubItems(intnum + 1) = ""
Else
listitem.SubItems(intnum + 1) = oRS.Fields(intnum)
End If
Next .MoveNext
Wend
End With
oRS.Close以上事例为显示到ListView中,也可以用其他控件
我还想问:
1、在Excel里有大量的数据,可窗口大小有限,怎样才能在OLE对象窗口中滚动地浏览数据?
2、怎样动态地在OLE中插入对象(就是说我点击“月度按钮”时插入的对象是 m.xls 表 ;点击“年度按钮”时插入的对象是 y.xls 表)