VB.net for Excel数据库中的慢循环. [英] Slow looping in VB.net for Excel database.

问题描述

VB.net for Excel数据库中的慢循环.

我是VBA初学者.这是一个VB.NET项目. DateCon工作表中有6205行.虽然我还有其他的工作表，大约有10万行.此循环花费了3分钟，而其他工作表中的其他循环则花费了更长的时间.有什么方法可以减少循环时间?

Slow looping in VB.net for Excel database.

I am a VBA Beginner. This is a VB.NET project. There are 6205 rows in DateCon sheet. While i have other sheets which have about 100k rows. This loop took 3 minutes while other loops in other sheets takes more longer. Is there is way to reduce loop time?

DateCon工作表如下:
AD中的日期 BS中的日期
2017/02/09 2073/10/27
......... ...........

对于我来说= 4至6205
           如果Xls.Worksheets("DateCon").Cells(Me.n，1).Value = Xls.Worksheets("DateCon").Cells(2，2).Value然后
                NepDate = Xls.Worksheets("DateCon").Cells(Me.n，2).Value
                Xls.Worksheets("SchoolConfig").Unprotect
                Xls.Worksheets("SchoolConfig").Cells(9，2).Value = NepDate
                Xls.Worksheets("SchoolConfig").Protect
           如果结束
       下一个我

谢谢

SK

The DateCon sheet is as like:
Date in AD Date in BS
2017/02/09 2073/10/27
......... ...........

For Me.n = 4 To 6205
            If Xls.Worksheets("DateCon").Cells(Me.n, 1).Value = Xls.Worksheets("DateCon").Cells(2, 2).Value Then
                NepDate = Xls.Worksheets("DateCon").Cells(Me.n, 2).Value
                Xls.Worksheets("SchoolConfig").Unprotect
                Xls.Worksheets("SchoolConfig").Cells(9, 2).Value = NepDate
                Xls.Worksheets("SchoolConfig").Protect
            End If
        Next Me.n

Thanks.

SK

SK Bhattarai

SK Bhattarai

推荐答案

你好，

从未尝试读取那么多单元格，因此以下内容可能会或可能不会有所帮助.当我使用Excel时，主要变量如下(

Have never tried reading that many cells so the following may or may not help. When I work with Excel the main variables are as follows (see also)

Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlCells As Excel.Range = Nothing

创建应用程序对象

xlApp = New Excel.Application

这是要尝试的部分，紧接在上面的行之后

Here is the part to try, directly after the line above

xlApp.ScreenUpdating = False

这篇关于VB.net for Excel数据库中的慢循环.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！