我的天

昨天说到当我想用VBA把一个工作簿里面的某些工作表复制到另外一个工作簿的时候，如果要复制的工作表有多个，复制过程中就会出现闪烁。如果需要复制的工作表很多，闪烁的时间会比较长。我觉得批量选择，最后一次性复制是一个解决办法。如果不是用VBA，而是手动实现这个复制功能，的确会用批量选择，然后再复制。现在按住Ctrl，然后用鼠标选择需要复制的工作表，最后进行复制。如果要在VBA里面实现这个批量选择，该怎么做呢？

一个工作簿里的工作表该如何判断哪些是需要的，哪些是不需要的，对我来说只需要判定工作表的名称就可以，所以我可以对需要的工作表以某种方式结合起来，比如形成一个以固定分隔符的字符串。VBA里要表达批量选择，然后复制，到底该用什么东西呢？这一次我没有搜索，而是直接录制了个宏。结果发现原来批量选择再复制，在宏里默认是通过数组的。需要把目标工作表的名称组合成一个一维数组，然后用最普通的复制方式把拷贝到新的工作簿。所以我要做的就是把目标工作表的名称赋值给一个一维数组。因为不知道到底要复制多少个工作表，所以这个数组应该是一个动态数组，我马上想到的方式就是以默认分隔符打断字符串的方式赋值给一维的动态数组。这个操作非常高效，而且动态数组的大小完全不需要在一开始就定义好，字符串里面有多少东西就可以生成多大的一维数组。

接下来，我要做的是经过一轮循环筛选出某个工作簿里我需要的工作表的名称，然后把这些名称以固定分隔符的方式连接起来。最后在打断之前我还要干掉一个分隔符，因为无论我把分隔符放在最前面还是最后面，整个字符串还是会多一个分格符。我不知道多一个分隔符在赋值给一维数组的时候会不会失败，作为完美主义者，我宁愿手动把那个分隔符去掉。那不过是一个字符串取值的简单操作而已。处理好字符串以后，就可以打断那个字符串赋值给一维数组，最后通过经典的复制方式，把工作簿里的目标工作表拷贝到另外一个工作簿。

我没有测试过这样处理耗时跟基础经典处理有多大差别，但就肉眼来说，差别挺大。因为如果进行这般批量选择一次性复制，基本上可以秒杀完成，但如果使用经典的方法、工作表又比较多，我感觉得闪烁一秒钟以上。秒杀就能完成我感觉那个耗时会在0.3秒以内。

批量选择再复制的确可以秒杀实现拷贝工作表，但是光标会停留在最后一个被复制的工作表里，我需要光标仍然停留在我的点击VBA控件的工作表。所以最后我又增加了一个激活目标工作表，把鼠标锁定在某个单元格的功能。这个功能不是非如此不可的，没有也不影响数据处理本身，但就方便程度而言，这是显而易见的。就如没有初始化和拷贝数据那两个模块，核心功能没问题，但是多了这些贴心的小步骤。整个核对的过程会变得更顺手。

如果使用的时候能更顺畅，我宁愿把代码搞得复杂一些。毕竟复杂的代码是一次性的。

PS：测试
复制19个工作表：批量法0.85秒，经典法7.25秒。
复制8个工作表：批量法0.59秒，经典法3.02秒。
复制4个工作表：批量法0.48秒，经典法1.59秒。

Sub 批量拷贝工作表-批量法()
    Application.ScreenUpdating = False '关闭屏幕刷新
    Dim book As Workbook
    Dim sht As Worksheet
    Dim my_name As String, stringArray() As String
 
    my_name = ""
 
    For Each book In Workbooks
        If book.Name <> "AAA.xlsm" Then
            For Each sht In book.Worksheets
                If Not sht.Name Like "*BBB*" Then
                    my_name = my_name & sht.Name & ","
                End If
            Next
            my_name = Mid(my_name, 1, Len(my_name) - 1)
            stringArray = Split(my_name, ",")
            book.Sheets(stringArray).Copy before:=ThisWorkbook.Worksheets("条件")
        End If
    Next
 
    With ThisWorkbook
        .Worksheets("条件").Activate
        .Worksheets("条件").Range("A27").Select
    End With
 
    MsgBox "完成数据抓取！"
    Application.ScreenUpdating = True '打开屏幕刷新
End Sub
 
************************************
 
Sub 批量拷贝工作表-经典法()
    Application.ScreenUpdating = False '关闭屏幕刷新
 
    Dim book As Workbook
    Dim sht As Worksheet
    For Each book In Workbooks
        If book.Name <> "AAA.xlsm" Then
            For Each sht In book.Worksheets
                If Not sht.Name Like "*BBB*" Then
                    sht.Copy before:=ThisWorkbook.Worksheets("条件")
                End If
            Next
        End If
    Next
 
    With ThisWorkbook
        .Worksheets("条件").Activate
        .Worksheets("条件").Range("A27").Select
    End With
 
    MsgBox "完成数据抓取！"
    Application.ScreenUpdating = True '打开屏幕刷新
End Sub

几年以后，我终于又在某一个我用来核对账本的VBA脚本上加了两个新的模块。之前这两个模块的内容我都是手动实现的。第一个模块是初始化，就是把已经核对过的明细数据删除掉。初始化这个步骤眨眼间就完成了，因为是在同一个工作簿里进行。另外一个模块做的是数据抓取，抓取我需要核对的那个工作簿里的明细工作表。我要核对的那个工作簿里的汇总，那个工作表默认不抓取，因为那是用来核对的。另外一个跟明细数据和汇总数据不是一伙的工作表也不能抓取，因为那个表的数据格式跟其它的不一样。过去这几年来我做的事情都是手动的在VBA脚本所在的工作簿里删除掉已核对的数据明细，然后再把我需要核对的那个工作簿里的明细粘贴到VBA脚本所在的工作簿。这个操作很机械很单一，完全可以用VBA去实现，但是我却一直没有这么干，为什么呢？大概是因为最初的时候，我写的那个VBA脚本核心部分实现的功能已经减少了我绝大部分的工作量，简单实现了非常麻烦才能实现的事情，所以我觉得自己已经上岸了，相对来说，这些复制粘贴删除的东西比较简单。简单归简单，很容易能实现，但是日积月累经常性习惯性每一次都要整那么十几二十遍也是很烦的。为了简化初始化的那个步骤，我每次核对完就以不保存的方式关闭VBA脚本所在的工作簿，然后重新打开。

我不知道对以前的我来说，写着两个模块有多难，对现在的我来说，对今年已经在VBA脚本里浸润了好几个月的我来说挺简单。实现方法很简单，但是出来的效果我感觉上应该还可以改进。初始化的那个脚本已经很完美，没啥需要再进一步了，但是那个拷贝数据的脚本，如果需要复制的工作表有很多，屏幕会出现长时间的闪烁。那种闪烁源于不同工作簿之间切换导致聚焦位置改变。之所以这样，因为我所使用的复制方式是VBA教程里最简单的那款。微软的默认教程就是那样，当然你也可以使用不一般的方式。闪烁是因为不同工作表之间切换以及活动工作表活动单元格之间的变换。这个问题之前我已经发现。我以前的VBA脚本里也有这种问题。做一个循环，把某个工作表名称符合要求的就复制到 VBA所在的工作簿，这个循环是显而易见的，但是一个一个工作表去判断，一个一个复制显然就会出现很多次闪烁。如果我先去判断，然后把符合要求的全部选定，最后一次性复制，这样会不会就可以从多次闪烁变成一次闪烁呢？多次闪烁你肯定能感觉到闪烁的存在，但如果只是一次，估计那种闪烁感就会感知不到了。有人在网上提出过如何避免闪烁这个问题，但好像大家都没有在批量选择然后一次性复制上面想。我也想参考他们的做法改进，但好像不太适合我的实际情况。

我觉得闪烁这个问题是可以攻克的。

当我正在潜心研究VBA+ADO+SQL的时候，某一天当我去搜索某个问题的解决方案，发现Excel里将要内置python了。那这个到底是什么样的python呢？星期四的晚上我看到ExcelHome的专业人士进行了进一步的解释。他们的专业人士说的东西，我还是比较认可的。

当我第一次听说Excel里面终于可以用python的时候，我并不太兴奋。为什么会这么说呢？因为是在Excel的某个地方写python，而不是在一个完备的IDE里写。这有什么问题呢？

可以这么说，Excel所有写代码的地方都让人挺不愉快。比如你要写公式，经常的情况是你选了那个，一会车，然后就没有了。如果你要写嵌套的公式，那些括号更加是会把你搞死。写公式的那个地方，你很难分得清全角和半角标点符号，于是你经常被卡在那个地方，然后被卡没了才发现自己在打勾之前没有先复制粘贴到其他地方，所以打那一大堆全部白费了。

在其它编辑器里也很郁闷。PQ有高级编辑器，高级编辑器好像要比公式智能一点点，但是又过于智能了，比如经常会帮助你自动添加一些符号，有可能是双引号，更多时候是逗号或者括号，但那并不是你想要的，你自己会把那补全。在你不知情的情况下，PQ给你添加上去以后，最终就报错了，翻找一大堆后才发现，那里不知道为什么多了一个符号。

PP里面写公式，如果是写度量值，情况还会好一点，因为你可以先做一个公式的校验，校验失败起码就意味着你基本上不用确定提交了，但是校验的时候到底是哪里不对，为什么不对呢？这得凭借你的经验。如果你进入到PP里面，在那个类似于Excel写公式的那个地方写代码，经常遇到的情况是你还没写完，就直接给你报错。因为报错了，你被卡住了，所以还真的漏了一些东西，结果当然是无果了。

相对于PQ和PP来说，VBA算是Excel里面可以写代码比较完善的地方。在VBA里有标识符和关键字，而且可以设置不一样的颜色，但问题是VBA那个编辑器除了会帮你增加空格以外，你别想能帮你进行任何的补全。无论函数名称还是那些你之前已经定义过的变量。被大家吐槽的最多的是一句判断没写完，要到其它地方复制东西，马上弹窗告诉你语法错误。当你运行不通过的时候，会有各种各样的弹窗，但绝大多数情况之下提示都没什么意义，因为没有针对性，你不知道该怎么改。

综上所述，在现有的Excel里面写代码，除非你非常牛逼，一写就对，万一你是那种粗心大意到极点的人，在Excel里写代码会让你非常崩溃。那个大家期待已久的python，据专业人士说，输入的方式是盲打。你别想Excel会给你补全，会给你提示任何错误。而且那个python in Excel是云上的东西，所以你的电脑上可以完全不安装python，数据通过网络云计算然后再返回结果。这就意味着算力最大的障碍就是你和服务器之间到底有多通畅？VBA完全可以脱机使用，那就意味着可以秒杀出结果，但如果我用的是python，需要实现同样的功能，我跟云服务器那边用有几万光年的代沟，甚至根本连不上，那么python那个地方永远都会显示#BUSY#，永远都不会有结果。如果一直挂在那里，或者某一时刻终于连上了，就会把结果反馈回来。我从来不太相信微软的服务器，因为从windows或者office软件系列的更新就可以看出，我们跟微软的服务器有非常深的代沟。现在这个刚刚有了雏形的python，居然是暂时只能用云计算，这样的东西能让人有多少期待呢？如果你不是在中国，如果你在美国，你是一个Microsoft 365的付费用户，而且是专业版的。可能会好很多，但是，我们在中国，我只能说希望越大失望越大。

我还是用好手头上的工具，实现我的小目标好了。

每次都定下一个小目标，然后去实现。结果发现一天多一点的时间居然就能搞定一个问题，这种进度有点出乎我意料，因为之前的那些问题让我挣扎了好长一段时间，起码有两三天，之所以后来进度加快了，大概是因为我明了了我要做什么，但是尽管是这样，还是会遇到很多奇奇怪怪的问题。

这周初我解决的是批量生成月度核对表。我把这个任务分成两个步骤，一个是查询到底要生成多少，接着就是把查询到的结果生成文件，但是那个结果跟文件又不一定是完全对应的，根据不同条件可能10条查询结果最终会生成9个表。因为实际上某些条件是需要合并才能得到我想要的文件。在怎么设定条件，如何进行循环方面，我纠结了好长时间，几乎可以这么说，上个周末我一直在做各种尝试，为的就是最终实现这个目标。

在完成了批量生成月报以后，接下来我要做的是生成某个仓的分仓台账。相对于之前的月度核对表，这个单仓台账相对而言条件是固定的，而且必定只生成一个文件。同样我首先做的也是做一个查询，查询一下这个仓到底有多少条记录是可以生成分仓台账的，我又要生成具体哪一条。有些是无法自动实现的，因为实际情况是某些仓某些筛选条件是不一样的，但实际上应该反映在同一个分仓台账里。这个时候就需要手动合并一下条件。这种例外的事件不确定会在什么时候发生，所以必须给手动留有余地。之所以分步骤，其实一个很重要的原因是其实有时并不是为了生成分仓台账，只是要查询一下这个仓的情况。批量生成月度核对表，我花了好几天的时间，但是生成分仓台账，我只花了一天不到。

接着，我研究的是库存查询。在不同的条件之下进行库存查询，最后的结果是以一个透视表的方式展现出来，根据不同的查询条件透视的项目不一样。虽然我想到透视的项目是不一样的，但实际上在我研究的过程中，我先在单一的条件上做尝试，当单一的条件生成的数据没有问题以后再把它扩充到动态条件。因为有了之前月度核对表的锻炼，所以动态条件该怎么做我是有点底的。

SQL最基本的查询语句基本上我已经比较熟悉，这一次库存查询最后一步需要做一个透视。透视这个东西是我之前没有尝试过的，虽然我是Excel数据透视表的超级粉丝，但是在SQL里面控制这个东西，我还是很不在行的。所以到底什么条件可以控制，可以控制到什么程度我是不知道的。教程通常都只是最简单的那些，用上面的数据你重复100遍都不会出什么幺蛾子，但是在实际情况下你会有更多需求。比如当我要控制被透视列的排序的时候发现好像在Excel的SQL里无法做到。即便我在透视之前那一步已经排好序了，但是透视的时候依然是我行我素。让我比较挣扎的是，在透视之前我已经通过分组合并计算出被透视的列的合计数了，但是透视之后合计混在了那一堆被透视的字段中间。最后我已经想到不计算合计，在SQL里面生成透视以后输出到数组，我在数组里面做合计。就在我几乎要放弃的时候，原来合计可以通过在透视的select里用一个聚合函数实现，这样的话透视之后的表格就是先是条件列，然后是合计，接着是那些被透视的列。虽然合计不是放在我想要的最后面，但起码放在了最前面。

库存查询研究过程中让我纠结的问题是什么，明天继续。

几天之前我第一次听说VBA+ADO+SQL可以组成一个非常牛逼的东西。当我听说要在VBA里面运行SQL的时候，我有怀疑过这是不是我以前遇到的那一款？但是在SQL的外面又有一个ADO，这就很不一样，因为ADO这个东西你甚至可以控制只执行一次，然后就自动消失.当然你可你也可以用打开的方式去折腾，但是对低端用户来说，光是执行就可以完成大多数事情。

在学习VBA的时候，首先会告诉你，如果要操作工作簿，就得先把它打开。用完不用了，你还得把它关上，不打开工作簿是没办法进行操作的。同样，在学习用python控制Excel的时候，也是这么个思路。按照正常人的想法，你要获取里面的东西，当然得开门，哪怕实际上你只是进去看一看，你并不改动里面的东西。比如在做查询的时候，实际上你不过是收集里面的资料，你不需要改动里面的任何摆设，把资料收集回来了以后你可以再加工，但是原始的那个东西是不变的。为什么跨表表查询的时候，我首先想到的是Power Query，就是因为那个东西本来就是开发出来做数据清洗的。核心的观点就是不改动原始数据，通过可重复的步骤，你就能得到规范的东西，然后再给其它玩意调用。但是VBA+ADO+SQL彻底颠覆了我过往的认知。因为这三个东西加起来就意味着你要获取工作簿里面的东西，你根本不需要打开它。通过链接的方式，就可以到达那个地方，虽然实际上这样是无法修改文件内容的，但是跟PQ一样，可以对已经获取的东西进行再加工。所以准确来说，虽然做出来的菜是不一样的，但实际上原料还是那些，牛逼的地方在于，那三个东西甚至连文件都不用打开，但是PQ在运行的时候，实际上是要打开文件。如果你的查询设置比较复杂，他还得把同一个源文件一次又一次打开。因为PQ里的查询是并行的，你没办法控制哪个先哪个后，所以当它们抢着打开同一个源文件的时候就会出现错误，就会出现刷新失败，然后说不准什么时候就会告诉你原始的数据格式不对，反正最终结果就是刷新失败，但是当你再次刷新的时候又好了。

VBA这个东西在我听说有PQ和PP之前就已经存在。大家可以通过VBA的方式批量打开文件夹批量合并里面的文件，但问题是合并是合并了，但是却非常不智能，因为最简单的模式只能保证内容都给你贴上去了，但是对不对得上你自己碰运气。比如第一个表跟第二个表某些列的顺序是不一样的，结果你就会拼出个寂寞，但是PQ和PP就不会犯这种错误。因为他们有连接方式这种概念，同时，它们所处理的不是某个单元格，主要操作的是字段，又或者说是列。当然，如果你条件足够，可以精确到记录。我在python里，只要把从Excel读取到的数据赋予pandas，接下来你就可以用 dataframe的方式去处理那些东西。我个人觉得，只要格式化了，只要那个数据框架定下来了，SQL、PP、PQ、python这4个好像不太一样的东西都能干非常类似的活儿。

首先是Power Query版本，接着是Power Pivot版本，第三个是python版本，现在我做的是VBA+ADO+SQL的版本。SQL in Excel是一个非常屌丝的东西，相比于其它软件里面的SQL，Excel里可用的公式我个人觉得会让人非常抓狂，是阉割版的SQL。本来就很难找到一个标准的说法告诉人你SQL in Excel到底有什么可以怎么用，当你用其它方式套在Excel里发现用不了的时候，才知道原来根本不能这样。对我这个新手来说，我只是大概知道SQL可以这样，应该这么整，但是套了一个Excel的条件以后就变成了大概应该是这样的事情实际上不行。

一天到晚都在为代码纠结。反而让我日子过得很实在，虽然运动和打游戏的时间被严重侵占了。