使用ADO 可以在DATAGRID 中查看到表中的信息:
Private Sub Command1_Click() Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
oConn.CursorLocation = adUseClient
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\A.xls;" & _
"Extended Properties=""Excel 8.0;"""
oRS.Open "Select * from [Sheet1$]", oConn, 3, 2 Set MSHFlexGrid1.DataSource = oRS End Sub接下来如何将表中查看到的信息提交到后台TABLE中呢(TABLE 已在后台SQLPLUS中建好) ?
Private Sub Command1_Click() Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
oConn.CursorLocation = adUseClient
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\A.xls;" & _
"Extended Properties=""Excel 8.0;"""
oRS.Open "Select * from [Sheet1$]", oConn, 3, 2 Set MSHFlexGrid1.DataSource = oRS End Sub接下来如何将表中查看到的信息提交到后台TABLE中呢(TABLE 已在后台SQLPLUS中建好) ?
Private Sub Command1_Click()
Dim Adocon As New ADODB.Connection Adocon.ConnectionString = "Provider = SQLOLEDB.1;Password = ; Persist Security Info = True;User ID = sa;Initial Catalog = DRUGDB; Data Source = 192.168.30.122"
Adocon.CursorLocation = adUseClient
Adocon.ConnectionTimeout = 120
Adocon.Open Adocon.Execute ("insert into newsort SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=C:\A.xls.xls;Extended properties=Excel 8.0')...[Sheet1$]")
Adocon.Close
Set Adocon = Nothing
End Sub
Dim Adocon As New ADODB.Connection Adocon.ConnectionString = "Provider = MSDAORA.1;Password =; Persist Security Info = True;User ID = su;Initial Catalog = DRUGDB; Data Source = *.*.*.*"
Adocon.CursorLocation = adUseClient
Adocon.ConnectionTimeout = 120
Adocon.Open
Adocon.Execute ("INSERT into MYTAB SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=c:\A.xls;Extended properties=Excel 8.0')...[Sheet1$]")
Adocon.Close
Set Adocon = Nothing
End Sub
但是我已检查过字段类型和大小在EXCEL 和mytab 中都是符合的 。
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\A.xls',[sheet1$])