gpt4 book ai didi

vba - Excel VBA 在单元格中打印即时窗口的结果

转载 作者:行者123 更新时间:2023-12-02 16:05:37 25 4
gpt4 key购买 nike

我有一个宏,用于计算每个查询运行所需的时间并将时间输出到即时窗口。

我有办法将其设置为输出到单元格吗?

宏:

Sub TimeQueries()
Dim oSh As Worksheet
Dim oCn As WorkbookConnection
Dim dTime As Double
For Each oCn In ThisWorkbook.Connections
dTime = Timer
oCn.Ranges(1).ListObject.QueryTable.Refresh False
Debug.Print Timer - dTime, oCn.Name, oCn.Ranges(1).Address(external:=True)
Next
End Sub

立即窗口输出:

 6.609375     Query1        [YEtest.xlsm]Query1!$A$1:$S$3006
15.12109375 Query2 [YEtest.xlsm]Query2!$A$1:$S$3006
21.0703125 Query3 [YEtest.xlsm]Query3!$A$1:$S$3006
0.125 Query4 [YEtest.xlsm]Query4!$A$1:$B$2

选项卡名称我希望将其输出到:Control

最佳答案

只需将 oSh 设置为工作表引用并使用 Cells 集合即可输出您想要的内容:

Sub TimeQueries()
Dim oSh As Worksheet
Dim oCn As WorkbookConnection
Dim dTime As Double
Dim lngCounter As Long

'set a worksheet reference - e.g. Sheet1
Set oSh = ThisWorkbook.Worksheets("Control")

'initialise counter
lngCounter = 1
For Each oCn In ThisWorkbook.Connections
dTime = Timer
oCn.Ranges(1).ListObject.QueryTable.Refresh False

'set output to cells on the worksheet
oSh.Cells(lngCounter, 1).Value = Timer - dTime
oSh.Cells(lngCounter, 2).Value = oCn.Name
oSh.Cells(lngCounter, 3).Value = oCn.Ranges(1).Address(external:=True)
lngCounter = lngCounter + 1

'Debug.Print Timer - dTime, oCn.Name, oCn.Ranges(1).Address(external:=True)
Next
End Sub

关于vba - Excel VBA 在单元格中打印即时窗口的结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42996281/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com