VB 操作 EXCEL

最新推荐文章于 2021-10-08 13:07:29 发布

Snoworld

最新推荐文章于 2021-10-08 13:07:29 发布

阅读量885

点赞数

分类专栏： Visual Basic 文章标签： vb microsoft excel security sql server command

本文链接：https://blog.csdn.net/hank212/article/details/668921

版权

Visual Basic 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

'引用ADO(Microsoft ActiveX Data Objects 2.X Library)
Private Sub Command1_Click()
    On Error GoTo err
    Dim cn As New ADODB.Connection
    '具体的数据库连接请自己改过来
    cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123;Initial Catalog=dataBase001;Data Source=mySERVICE"
    cn.CursorLocation = adUseClient
    cn.Open
    cn.Execute "select * into NewTable from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=c:/Test.xls;','select * from [Sheet1$]')"
    cn.Close
    Set cn=Nothing
    Exit Sub
err:
    MsgBox err.Description
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'工程--->引用--->Microsoft ActiveX Data Object 2.x（版本号）
Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "/test.xls;Extended Properties='Excel 8.0;HDR=Yes'"

    cn.Execute ("insert INTO [odbc;Driver={SQL Server};" & _
        "Server=ljx;Database=test;" & _
        "UID=sa;PWD=].sqlTable " & _
        "select * FROM [Sheet1$]")
    cn.Close
    Set cn = Nothing
End Sub
-----------------------------------------------------------------------------