Excel VBA打印单元格中立即窗口的结果 [英] Excel VBA print results of immediate window in a cell(s)

问题描述

我有一个宏，每次查询需要多长时间才能运行并将时间输出到直接窗口。

I have a macro that times how long each query takes to run and output the time to the immediate window.

我有一种方法将其设置为输出到细胞？

I there a way to set it to output to cells?

宏：

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

选项卡名称我希望它输出到：控制

Tab name I want it output to: Control

推荐答案

只需设置 oSh 到工作表参考，并使用单元格集合输出您想要的内容：

Just set oSh to a worksheet reference and use the Cells collection to output what you want:

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

这篇关于Excel VBA打印单元格中立即窗口的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！