ADO功能..复制，过滤，从封闭的工作簿粘贴到活动的wrokbook [英] ADO functionality.. copy, filter, paste from closed workbook to active wrokbook

问题描述

我目前正在寻找以下解决方案的替代方案，但使用ADO功能，以使源工作簿未打开。我想象会减少处理时间？

I am currently looking for an alternative to the solution below, but using the ADO functionality so that the source workbook isn't opened. I am imagining this will decrease processing time?

你的想法..

谢谢

Sub CopyFilteredValuesToActiveWorkbook()

    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim rngSource As Range, rngDest As Range

    Set wbSource = Workbooks.Open("\\Linkstation\rrm\X_DO_NOT_TOUCH_CC\MasterLogFile\Masterlogfile.xlsx", , True) 'Readonly = True
    Set wsSource = wbSource.Worksheets("LogData")
    wsSource.Range("$A$1:$H$3").AutoFilter Field:=3, Criteria1:="Opera"
    Set rngSource = wsSource.Range("A:Z")

    Set wbDest = ThisWorkbook
    Set wsDest = wbDest.Worksheets("MLF")
    Set rngDest = wsDest.Range("A:Z")

    rngDest.Value = rngSource.Value 'Copies values over only, if you need formatting etc we'll need to use something else

    wbSource.Close (False) 'Close without saving changes

    End Sub

推荐答案

你缺少这一行：

Set rs = CreateObject("ADODB.Recordset")

由于某种原因，Win XP将无法运行。应该放在 con.Open conStr 之后。

For some reason Win XP will not run without it. It should be placed right after con.Open conStr.

这篇关于ADO功能..复制，过滤，从封闭的工作簿粘贴到活动的wrokbook的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！