VB 调用包含连接EXCEL的SUB过程速度慢
Private Sub Command1_Click()Dim MT1 As Date
Dim MT2 As Date
'MT1 time
MT1 = Time
Call GetData.FromExcel
'MT2 time
MT2 = Time
MsgBox "Total time " & DateDiff("s", MT1, MT2) & " seconds"
End Sub
'/-------------------------------------/
Sub FromExcel()
Dim MT1 As Date
Dim MT2 As Date
'MT1 time
MT1 = Time
'开始获取EXCEL数据
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("excel.application")
Set xlBook = xlApp.Workbooks.Open(App.Path & "\test.xls")
xlApp.Visible = False
Set xlSheet = xlBook.Worksheets(1)
Dim iA As Integer
iA = xlSheet.Range("a65535").End(xlUp).Row
Dim arrA As Variant
arrA = xlSheet.Range(xlSheet.Range("a1"), xlSheet.Cells(iA, "a"))
For Each arr In arrA
Form1.Combo1.AddItem (arr)
Next
xlBook.Close (True)
xlApp.Quit
Set xlApp = Nothing
'结束获取EXCEL数据
MT2 = Time
MsgBox "Excel time " & DateDiff("s", MT1, MT2) & " seconds"
End Sub
'/-------------------------------------/
excel time 是0-1秒，但是总时间竟是5-8秒，不解！
谢谢！ --------------------编程问答-------------------- debug.print now
Set xlApp = CreateObject("excel.application")
Set xlBook = xlApp.Workbooks.Open(App.Path & "\test.xls")
xlApp.Visible = False
Set xlSheet = xlBook.Worksheets(1)
debug.print now
看看创建EXCEL对象并打开EXCEL文件花了多长时间。 --------------------编程问答--------------------
谢谢老大快速回复！
问题不在获取EXCEL数据，而是调用SUB获取EXCEL数据之后有个时间滞留 --------------------编程问答-------------------- 对com组件的操作，就是这样，避免不了。
加一个进度条或者splash窗口吧。以免让用户等太久。 --------------------编程问答-------------------- iA = xlSheet.Range("a65535").End(xlUp).Row
Dim arrA As Variant
arrA = xlSheet.Range(xlSheet.Range("a1"), xlSheet.Cells(iA, "a"))
For Each arr In arrA
Form1.Combo1.AddItem (arr)
Next
这一段应该非常慢吧, 循环次数本身确实并不大,不过每次都要调用控件方法,这效率就太低了. --------------------编程问答-------------------- --------------------编程问答-------------------- For Each arr In arrA
Form1.Combo1.AddItem (arr)
Next
改成
Form1.Combo1.list=xlSheet.WorksheetFunction.Transpose(arr)
试试能运行不?
补充：VB , 数据库(包含打印，安装，报表)
