不太明白你在foxtable论坛中咨询这个问题的原因,
猜想是后台调用Excel时,由于Excel有大量的公式,导致文档打开速度过慢?
在EXCEL文件->选项->公式页面中,将自动计算改为手动计算,可以加速文件的打开速度。
另外有一些复杂的公式,可以采用在文件中设置宏进行计算的方式,这样可以避免大量的计算情况,需要计算时直接运行宏即可。
比如,我有一项工作,是根据不同的项目(每个子项目一张工作表,每个项目子项目数不固定),有一个汇总页批量加权计算所有子项目的和。
由于公式的跨表计算太复杂,就写成了宏进行该项计算,然后通过foxtable调用该VBA完成计算,测试该计算时间从10+min,变到了30s左右。
Sub Refsh_date()
On Error Resume Next
Dim inum As Double
Dim xn As Double
Debug.Print Time
Application.Calculate
Application.Calculation = xlCalculationManual
ThisWorkbook.Worksheets("Carline").Unprotect Password:="Pec8888!!"
For irow = 11 To 147
With ThisWorkbook.Worksheets("Carline")
If .Cells(irow, 4) = "" Then GoTo SkipLable:
If .Cells(irow, 5) = "A" Then
For icol = 10 To 138
If .Cells(7, icol).Value <> 0 Then
inum = 0
For J = 6 To 25
sheet_name = Sheets("商品").Cells(J, 3)
If sheet_name <> "" Then
If Sheets(sheet_name).Cells(irow, icol) <> "" Then
inum = Sheets(sheet_name).Cells(irow, icol) + inum
End If
Else
Exit For
End If
Next
.Cells(irow, icol) = inum
inum = 0
End If
Next
End If
If .Cells(irow, 5) = "B" Then
For icol = 10 To 138
If .Cells(7, icol).Value <> 0 Then
inum = 0
xn = .Cells(11, icol).Value
For J = 6 To 25
sheet_name = Sheets("商品").Cells(J, 3)
If sheet_name <> "" Then
If Sheets(sheet_name).Cells(irow, icol) <> "" Then
inum = Sheets(sheet_name).Cells(irow, icol) * Sheets(sheet_name).Cells(11, icol) / xn + inum
End If
Else
Exit For
End If
Next
.Cells(irow, icol) = inum
inum = 0
End If
Next
End If
SkipLable:
End With
Next
Debug.Print Time
Dim sh As Worksheet
sh = ThisWorkbook.Worksheets("Carline")
ThisWorkbook.Worksheets("Carline").Protect Password:="Pec8888!!", DrawingObjects:=True, Contents:=True, Scenarios:=True, userInterFaceonly:=True, AllowFormattingColumns:=True
sh.EnableOutlining = True
On Error Resume Next
For Each sh1 In Sheets
sh1.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
Next
Application.Calculation = xlCalculationAutomatic
End Sub