我的天

对系统的导出数据进行了两种形式的格式化以后，接下来我要把其中一种格式化的VBA交给同事，告诉她该怎么用。在我印象之中，每天他们报送数据的时候用的电脑装的是office软件。我这里说的报送，意思是要得出某个文字转化版本的那些人。单位是很奇怪的存在。联想电脑附带的软件是office家庭学生版的正版，另外一些系统不知道为什么一开始的时候就被格式化了，被某些人装了windows系统之后装过各种奇奇怪怪的软件，最终可能定下来的是一个不知道什么版本的wps。也不知道从哪一年开始单位就有批量买的wps，但因为我是office软件的超级粉丝，wps根本无法满足我的要求。因为wps没办法做Power三剑客。Power Pivot还好一点，我用得比较少，但是如果要对一些数据进行文本合并，SQL没那么容易，需要用字典搭配字符拼接才能实现。在那种情况下，我首选通过Power Query合并。当然用Python也能做到，但是如果某个人的电脑里面装的是office，又在2019以后，PQ内置功能，但如果要别人运行Python，还需要安装，还需要配置环境。

我没想到的是那个同事居然先拿回办公室试一下，她办公室电脑用的是wps。wps我猜她安装的版本是32位，因为我的VBA查询用了ADO+SQL，这两个东西意味着不是所有的wps都可以直接执行。即便单位买的那个wps的版本能运行宏，但是宏里面又用了SQL，这个估计就很悬，最终能不能用，可能也可以但估计得折腾一番。幸好她没有挣扎，直接拿着那个VBA文件就直冲工作地点。她第一次在办公室截图回来告诉说wps无法运行宏的时候我就下去找人，结果没找到，原来她已经直接去工作地点了。过了一阵她又跟我说运行不了，反馈出来的那个信息就是VBA脚本没有找到恰当的源文件。

我第一个反应是她导出的文件会不会文件名有一些空格没有留意到，于是我马上赶往那个地方，结果发现她的确导出了文件，但是她发现导出文件的名字跟我之前跟她说的不一样，所以她重命名了。重命名没有任何的意义，因为导出的文件名不一样，基本可以确定她导出的位置不对。当我问她是在哪里导出的时候，她自己估计也马上发现了问题所在，果然就是因为她导出的文件导出错了。

工作地点的那台电脑一次就通过VBA考验。旁边的那台电脑是一台新的联想，预装office2021家庭学生版，但是那台电脑打开VBA之后就会有一条红色的提示。正常情况下，理论上那应该是一个黄色的提醒，按确定或者允许之类就可以了，但红色的提示好像根本没办法按掉。只要按那条红色的东西，就会弹出一个网页，告诉你一些细节。一开始我没有留意，我不管那条红色直接去选项那里设置允许宏，但即便这样，这个文件也依然运行不了。我就不得不去仔细看红色那条提示对应的那个网页。发现是因为因为出于某些安全原因，系统锁定了这个带宏的文件，需要在文件那里按右键点击属性，然后再点击某个解除按钮，之后才能运行那个文件的宏，打开文件才会出现黄色的提示。所以到底是什么安全设置让那个电脑带宏的文件出现那条红色的提示呢？起码在之前我接触过的所有office里未曾遇到过。

对用宏文件的人来说，宏可能有风险，但是对我来说，宏是我自己写的，我不可能不放心。

业务系统由从一期变成二期，摆在面前是两个格式化操作。当我做完第1个以后，我心里就很有底了，我知道第2个大概应该怎么做，也想清楚了输出的那些数据到底要放在哪里。一期的时候，我的原始数据从同事的Excel查询文件里获取，现在我需要做的就是多一步。首先得把数据从系统里导出，然后再执行我的VBA。

数据从系统导出，供VBA使用这里其实也是有个窍门的，一开始我也没想好，但是用了几天以后，我大概明白了。首先我得保证VBA跟源文件在同一个文件夹，这是我在脚本里写死的。其次，VBA文件只能是那个名字，只能是那个后缀，有一点不合适程序就会判定找不到那个文件，然后就会在那个文件夹里面建一个9KB大小的文件，名字是我需求的那个，实际上那个东西是打不开的，所以对新新手来说有迷惑性，但是当我一次又一次的遇到这个以后，我就习以为常了，看到那个9KB的东西我知道肯定因为我导出来的文件有些不对。

以前的浪潮系统我已经默认了只能用360极速打开，因为一期的时候他们只支持IE，而我用的浏览器是Firefox。不知道二期到底支持什么浏览器，反正我基本已经把360极速作为业务系统和OA系统的默认打开方式，其它东西基本不会通过那个打开。

因为导出的文件都是同样的名字，所以如果在某个文件夹里面不断保存，浏览器会很聪明地给第2个文件加个1，第3个文件加个2。我本想把那个文件覆盖掉，但360就是不让。因为我的电脑是默认显示文件后缀的，当我把那些有括号的1和2删除掉的时候，非常有可能就会删漏个空格，于是这就会导致VBA文件要读取源数据，实际上却找不到，接着就生成了那个9KB的玩意。怎么避免这个问题呢？这需要两个文件目录，我把浏览器下载的那个文件放在目录A。VBA文件放在目录B，目录B实际上也有一个以前的导出文件。把文件导出到目录A，然后再把目录A这个文件剪切到目录B。这种情况之下，windows允许我替换掉文件。那么我就能保证VBA能够找到适合的源，因为目录A已经没有了那个导出的文件，所以下一次下载的时候浏览器也不会给我自动添加后缀。虽然多了一个剪切粘贴的操作，但是整套流程下来非常顺畅，不会出现幺蛾子。在这种情况下，我的VBA依然无法正常运行，基本就意味着浪潮可能修改了那个查询，非常有可能是修改了字段名。比如切换系统的第1天，我很早就发现单据编号是错的，但是他们不能在我们作业的时候就上补丁，只能等我们作业完了才能修改，所以那天我只能用错的文件编号先做一波，然后第2天再重新来一次。当我第2天早上重新来的时候，发现又多了一些东西，单据编号改过来了。车船号本来在一列，终于分开了，但是当有车没船号或者有船没车，再或者车船号都没有的时候，没有在数据那写横杠。我还是得自己加工。一开始那里只有品种，补丁上去以后多了个二级品种，二级品种对我来说是毫无用处。最后一个修改的地方就是之前叫仓号的那个字段现在变成了货位。正是因为仓号变成了货位，车船号字段名没了，才让我的VBA读取数据的时候失败。他们每一次升级都不会告诉你，你也不知道他改动了什么，也就只有发现不对劲的时候再去见招拆招。

适应能力不强，还真的没法玩这些东西。

虽然写Excel VBA已经有好长一段时间，但实际上对一些很基础的知识我还是不扎实的。比如ThisWorkbook和ActiveWorkbook到底是怎么指定的。光说这两样我好像明白，但是当我要使用的时候就感觉老碰钉子。这个东西使用不当，就会经常被弹出错误说某个工作簿里面没有某个工作表，或者说某个工作表已经存在了，不能再新建。不同的写法意味着同样的VBA脚本发起的那个工作簿不一样，有可能要在打开有数据的那个工作簿里发起，又或者直接是在VBA脚本所在的工作簿发起，不需要打开源数据工作簿。除了这个以外，还有就是当刷新数据要放在你已经建立的工作簿的超级表里，该如何删除超级表之前的数据？有两种选项，一种是把数据全部清空，这种情况不改变超级表的格式的，之前已经使用的行会继续存在。这里用的是clear命令，另外一个则是用delete的命令，那样的话原来工作表里面的数据全部删除，只剩下最后一行。二者的差异就在于，如果是用delete命令，如果那个超级表下面还有数据的话，那些东西将会上移，如果你又在那里插入新的数据，可能就会乱成了一团。所以如果在一个工作表里，你安排了很多个超级表，而你又确认那些超级表的格式不会改变，基本上也就那几行，又或者你不确定到底有多少行，但你能确定行数肯定不会超过多少行，这样的话，当你要用VBA把刷新的数据同时写入这些超级表的时候就应该用clear命令，这样的话，刷新的数据就可以指定从哪里开始。如果在这种情况下，尤其是工作表的下面还有一个工作表，你用了delete命令那么当你清空数据再写入数据，下面那个超级表对应的那个区域肯定会乱来。

没人跟我说过这些细节，这是在我摸索的过程之中，经历过一次又一次撞板之后得出的结论，所以如果可以的话，超级表你可以在旁边建超级表，但你不要在超级表的下面建超级表，尤其是那种你根本说不准上一个超级表到底有多少数据的情况下。多建几个工作表，在不同的列起始不同的超级表，一点问题都没有，但在很多人的固有思维里面，Excel就只是一个放表的工具，上下左右都可以放，但是就数据处理的便捷和难易程度来说，超级表的放置其实是得遵循一定规则。

自从知道了ADO+SQL之后，我就经常把这两个东西作为我最大的杀器，把经常使用的Excel表当数据库。但Excel始终不是数据库，最大的差别我觉得是虽然ADO+SQL可以用类似数据库的方式理解Excel表格，但因为Excel表格可以变换不同的格式，所以当在某一列理论上都是数字的那里突然意外有一格被插入了文字，那么ADO+SQL进行转换的时候就会出毛病。刚好你又对那一字段进行了分组汇总，那程序必然进行不下去。通常情况下，人肉很难发现这个问题，因为不知道在哪一行，突然插入一个文字是个意外。一般情况下，调用ADO+SQL把工作表里面的区域读取格式化的时候没有理会超级表的范围。如果有进一步的限定，实际上是可以设定范围的，但一直以来我都没有进行这个行的主动限制。

掌握了SQL以后我觉得Excel自带的那些公式实在太复杂了，参数很多、形式变来变去，有些用乘号，有些用加号，有些用*。

摆在我面前的格式化操作，从大的来说是两个格式化，从小的来说其实二者又有点区别。我自己的那个格式化是把明细数据整理好。因为导出的那些信息部分是完全缺失的，所以我需要建立新的字段，数据为空，后续手动录入，另外一些则属于没有直接给我数据，但是我可以根据已有的数据经过一系列的逻辑判断得出我需要的字段。相对来说，我的格式化需求要高一点，要慢慢来，因为字段的判定只要一急就会出错、不全面。另外的一个格式化则属于要做出一个汇总的数据。一开始我并不确定到底他们需要哪些信息，开会的时候我翻桌子了，他们意识到这个问题，也翻桌子了，我大概知道他们的用途是什么，因为好像那个东西我参与过，但具体内容是什么我不清楚。我需要他们给我一个截图，或者把已有的文件发给我，我自己去看到底是什么内容。

在他们把那个版本发给我之前，实际上我已经在琢磨怎么整那个。一开始我想到的是选择某些列得出一个明细，然后根据那个明细自动生成一个透视表，透视表里面的内容肯定足够他们填写相关的信息，但关键是他们想要的不是数据表，因为数据表很难复制粘贴。平时他们需要把那些东西转化为了一段文字贴到群里，数据透视表看上去的确更直观也不会错，但关键是图片这个东西很占空间，如果你几天不保存图片就会失效，所以如果要长期保存数据，还是纯文字比较靠谱。

一开始我尝试的是数据透视表，数据透视表很简单，但关键是不同版本的Excel数据透视表出来的默认格式不一样。他们干的这种事需要数据数据透视表不显示分类汇总，同时得重复所有标签。手工操作的话，这个很简单，但是用VBA控制的时候却发现自动生成的那些脚本又长又臭。那些又长又臭的东西到底能不能简化一些呢？VBA这个玩意的确可以通过录制生成，但关键是通过录制生成的那些东西通常都速度很慢，有很多废话，有些时候可能不太精准。所以我折腾了半个晚上，到底怎么把那些自动录屏出来的宏简化一些。虽然已经很努力，但是有个数据透视表不做分类汇总的那个坎好像挺难迈过去。

后来当他们把正在使用的那套查询数据库表发给我以后，发现或许我根本不需要生成透视表，实际上一个SQL的分组合并就能实现他们想要的东西，从那个查询表的内容看来，我的那个同事也是为他们做了这个事情。所以，整个逻辑就很清晰了，首先要看他们需要的那些数据在哪几列，刚好导出的那个大表上面的数据就能满足他们的要求，只是字段名有些不一样而已。这个很简单。所以逻辑就是把数据导出，删除最后一行，因为那个是汇总没有任何效果，然后把有数据的列全部转化为数字格式。因为神经病的浪潮为了他们自己方便所有列都设置为了文本，文本分组合并求和会完全不会起效。思路很简单，首先从原数据那里选取我需要的列，然后进行一些字段的重命名以及一些数字的转化分组合并，最后输出到我想要的地方。

一开始我并没有想好到底要输出到导出那个文件还是我有查询脚本的那个文件。经过一番的纠结和尝试以后，我感觉直接在我查询的文件通过ADO的方式查询那个导出的数据表，然后在查询的那个地方直接输出最便捷。一开始我想到的是要他们把转化好的内容贴到目标的表格，然后刷新一下。后来发现既然我都用了ADO。我根本不需要他们打开导出的原始表格我就能获取数据。

思路清晰以后，接下来的就只是执行。

昨天说到一个很简单的SQL语句引用的数据库就只有一个字段两行记录，居然需要24秒才能得出结果。这让我觉得非常不可思议。首先可以肯定的是数据量非常少，为什么会出现这种问题呢？那只能是连接方面是不是出了什么故障，也不能说，那是失效的，因为的确还能查询得到想要查询的东西。在我测试的那个宏里面。我引用了两个文件，一个是外部文件，一个是内部文件。外部文件是含有比较多的数据，而内部文件，也就是我一开始说的那个只有两条数据。我感觉如果我的SQL再厉害一些，我对VBA再熟悉一些的话，那个内部文件可能我就不需要引用了，我直接就在VBA里创建一个数据库，然后把两条数据给写进去，用完以后就删掉，但显然现在我还没有很大的把握，一定能完美地做这件事情。把我某个文件里面的数据转化为数据库的数据我又烂熟，所以我采取了现在使用的这种方式。

ADO+SQL的这种方式，因为我们是跨表引用，所以意味着数据肯定来源于多个文件。他们有可能是同一个工作簿的不同工作表，也有可能是在不同的工作簿里。对我来说，只要是在一个工作簿里，那么起码一开始设定指向的时候就得有一个数据源。最经典的方式引用的那个数据源在使用数据的时候，在from后面不需要进行进一步的引用，其它的就得麻烦一些。我的第一个反应是，是不是引用数据的那个语句出现了变动呢？比如说现在我用的是Excel12。在数据源引用方面，我又折腾了一番，发现好像还是那样，没什么进展。会拖慢查询的那个数据源，我甚至把它放到了主数据源里，结果发现还是很慢，于是这就排除了是数据源引用语句变动导致缓慢。

所以这到底是什么原因造成的呢？因为我有很多个跨表引用的查询。有些查询是内部数据外部数据都有，有些只有外部数据，经过测试后我发现好像只有引用了内部数据的查询才会变慢。

为了证明我这个想法，星期三的晚上我编造了一些数据做测试。主要原理就是研究是不是数据源的关系导致这种变慢。一开始我的设计就是一个排列组合的方式，因为我默认的数据引用是要跨表的，所以我把数据源根据内内、内外、外外和外内这4种方式测试，实际上内内和外外是一回事，也就不需要进行两个引用了，所以我又把那两个东西拿了出来，同样进行测试。结果让人有点吃惊，凡是有内部数据参与的查询都会变慢。我测试的数据就只有一个字段几条记录，内内和内外需要12秒，外外需要0.1秒，外内需要24秒。这就能解释为什么我的那些变慢的查询起码都要24秒才能出结果。因为我永远把内部数据放在后面。究其原因是因为我设计那些查询的时候，我后来才想到要在那个查询文件里面搭一个加脚手架，把一些基础的东西加上去，在这种情况下我加得最多的是日期表。

关于这个测试的来龙去脉以及最终的结果，我在ExcelHome里面做了一个详细的帖子，在这里就不再具体阐述了。

折腾了这么一番以后，我发现这个锅还真不是我整出来的。造锅的是微软，不知道更新出了什么状况导致了。

Excel用多了，不知不觉我也居然能挑出微软的毛病。