| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 1431 人关注过本帖
标题:快速和慢速导入Excel表到ACCESS的区别!
只看楼主 加入收藏
huangyz_xy
Rank: 2
等 级:论坛游民
帖 子:101
专家分:30
注 册:2016-10-2
结帖率:77.78%
收藏
 问题点数:0 回复次数:0 
快速和慢速导入Excel表到ACCESS的区别!
今天有空发两段代码分别为快速和慢速导入,不是专业做这个玩意儿的,纯属爱好者中的菜鸟,不喜勿喷!

程序代码:
Public Function ImportExcelS(FileName As String, _
                            SheetName As String, _
                            TableName As String, _
                            k As Double)
    Dim xlsApp   As Excel.Application
    Dim xlsBook  As Excel.Workbook
    Dim xlsSheet As Excel.Worksheet
    Dim M, N As Double
    Dim rng As Range
    Dim I, J As Double
    Set xlsApp = CreateObject("Excel.Application")   
    Set xlsBook = xlsApp.Workbooks.Open(FileName)  
    Set xlsSheet = xlsBook.Worksheets(SheetName)      
    Set rng = xlsSheet.UsedRange
    I = rng.Rows.Count
    J = rng.Columns.Count
    If Conn.State <> ADODB.ObjectStateEnum.adStateClosed Then Conn.Close
    Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & App.Path & "\datas\Data_Source.mdb" & "';Persist Security Info=False"
    Conn.Open
    If rst.State = adStateOpen Then rst.Close
    rst.Open "SELECT * FROM " & TableName, Conn, adOpenKeyset, adLockOptimistic
    M = 6
    N = 1

    For M = k To I
        rst.AddNew

        For Each Fn In rst.Fields

            For N = 1 To J
                If xlsSheet.Cells(1, N) = Fn.name Then
                    rst.Fields(Fn.name) = xlsSheet.Cells(M, N)

                    Exit For

                End If

            Next N
        Next

        rst.Update
        FrmImportData.lblStatus.caption = "Status:" & M & " / " & I
    Next M

    Set xlsSheet = Nothing
    xlsBook.Close
    Set xlsBook = Nothing
    xlsApp.Quit
    Set xlsApp = Nothing
End Function
============================================================================================================
Public Function ImportExcelF(FileName As String, SheetName As String, TableName As String, k As Double)
    Dim xlsApp        As Excel.Application
    Dim xlsBook       As Excel.Workbook
    Dim xlsSheet      As Excel.Worksheet
    Dim Arr(1 To 100) As Double
    Dim M, N As Double
    Dim rng As Range
    Dim I, J, a, b, z As Double
    Set xlsApp = CreateObject("Excel.Application")  
    Set xlsBook = xlsApp.Workbooks.Open(FileName)  
    Set xlsSheet = xlsBook.Worksheets(SheetName)      
    Set rng = xlsSheet.UsedRange
    I = rng.Rows.Count
    J = rng.Columns.Count
    If Conn.State <> ADODB.ObjectStateEnum.adStateClosed Then Conn.Close
    Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & App.Path & "\datas\Data_Source.mdb" & "';Persist Security Info=False"
    Conn.Open
    If rst.State = adStateOpen Then rst.Close
    rst.Open "SELECT * FROM " & TableName, Conn, adOpenKeyset, adLockOptimistic
    M = 6
    N = 1
    a = 1

    For Each Fn In rst.Fields

        For N = 1 To J
           Arr(a) = 0
            If xlsSheet.Cells(1, N) = Fn.name Then
                Arr(a) = N             
                z = a                 

                Exit For

            End If

        Next N

        a = a + 1
    Next

    'MsgBox Arr(1) & "," & Arr(2) & "," & Arr(3) & "," & Arr(4)
    For M = k To 50
        b = 0
        rst.AddNew

        For Each Fn In rst.Fields

            b = b + 1
            If Arr(b) <> 0 Then
                rst.Fields(Fn.name) = xlsSheet.Cells(M, Arr(b))

            End If

        Next

        rst.Update
        FrmImportData.lblStatus.caption = "Status:" & M & " / " & I
    Next M

    Set xlsSheet = Nothing
    xlsBook.Close
    Set xlsBook = Nothing
    xlsApp.Quit
    Set xlsApp = Nothing

End Function
搜索更多相关主题的帖子: Set Excel Dim Conn If 
2019-10-20 00:12
快速回复:快速和慢速导入Excel表到ACCESS的区别!
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.053004 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved