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

本文介绍了VB.net for Excel数据库中的慢循环.的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

推荐答案

从未尝试读取那么多单元格，因此以下内容可能会或可能不会有所帮助.当我使用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数据库中的慢循环.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

XLS