在Excel中应用VBA批量导入数据

类别:.NET开发 点击:0 评论:0 推荐:

在Excel中应用VBA批量导入数据

 

 

马维峰

 

1.       问题由来

当一个漂亮MM向你请教如何录制并修改一个宏,把她每次的实验数据(几十个数据文件)导入Excel时,你感慨道:“很多Excel专家会录制一个宏来解决问题,然后每次使用的时候修改代码并粘贴到需要的地方,对于一个合格的程序员,这是最要命的事情。”

漂亮MM打断并告诉你,她不是程序员,也不想做程序员,然后命令你开始工作。

2.       通过录制宏导入数据

对于这个无法拒绝的MM,你只好垂头丧气的开始面对要解决的问题,想着MM几年后博士毕业,年薪至少5万,干个3、5年,年薪10万,还有项目提成,平时吃饭、打车、买可乐都可以报销,当然不会像你放弃了自己的专业,做了一个为生计奔波的程序员。

数据文件是仪器生成一系列文本格式的数据文件,格式完全一样,目的是要把每个数据文件导入到Excel中作为一条记录,也就是一行。那么,你想,可以用VBA写一个程序,然后定位到需要的位置,读入需要的信息就可以了[①]。你打开Excel,打开VBA编辑器,准备开始写代码。

“开始录宏吧”,MM提醒了心不在焉的你,你沉默了0.1秒,默念了一下VBA的信条:“万不得已不要写代码,尽量使用Office的功能”。于是你启动Excel打开这个文本文件,按照弹出的文本文件导入向导对话框的步骤,使用固定列宽导入了需要的数据。数据包括2部分,第一部分是文件头,包括一些数据信息,后面是按行放置的数据,包括结果和误差,MM要的是后边的数据,要把每行的数据和误差放置到相邻的两列(见下图)。

图 1数据文件部分和需要在Excel中的结果数据

 

明白了问题,一切就好办了,打开Excel,然后开始录制宏:首先打开文件,通过导入文本文件向导,读入数据,将特定单元格的数据拷贝到一个目标Excel文件中,然后关闭这个文本文件,停止录制宏。

录制的宏很长,大概包括2部分。第一部分是一句打开文件,格式转换的操作,后边一部分是激活不同的文件,拷贝和粘贴不同的Range。你删除掉刚才拷贝进来的数据,运行了一下这个宏,很好,需要的数据进来了。

3.       修改宏导入成批数据

MM提醒你,这个她也能做到,但怎么样把所有的数据文件都导入进来。你看看她带来的文件,文件名是“r20041124001357.txt”、“r20041124001358.txt”、“r20041124001359.txt”、“r20041124001360.txt”之类,大概是时间加序列吧。

你想,嗯,写一个循环就可以了,你打开了刚才录制的宏,检查了一下MM带来的文件,文件名最后2位从46到89,你可以写一个i从1到44的循环,把读入文件部分的文件名改为:

"r200411240013" & ( i + 45 ) & ".txt"

把粘贴目的地(range)表示行数的数字用i替换。

OK,你按下了执行按钮,每次关闭文件的时候,有一个讨厌的是否保存文件的对话框跳出来,其他好像一切正常,还好,点击了44次鼠标后[②],MM得到了需要的数据。

4.       修改VBA代码实现一个可通用的宏 4.1.    指定要导入的文件

当MM向你请教如何更改循环以导入不同的文件的数据时,你程序员的劣根性又开始冲动,你想通过一个打开文件对话框来指定需要的文件。你觉得实现应该不复杂,通过一个打开文件对话框,选择一系列文件,然后将文件全路径存入一个集合或数组,然后循环读出这些文件就可以了。

你先创建了一个窗体,然后放置了一个按钮,将CommonDialog控件引入工程,添加到窗体,在按钮的点击事件里加入如下代码:

 

    Dim strFiles As String, i As Long

   

    With CommonDialog1

        .Flags = &H200& Or &H80000    '可以选择多个文件

        .ShowOpen

       

        If .FileName <> "" Then

            strFiles = .FileName

        End If

    End With

 

    '分割返回值,返回值为以ASCII码为0的分割的字符串

       '字符串第一个为路径,之后为单个文件名

    Files = Split(strFiles, Chr(0))             

    For i = 1 To UBound(Files) Step 1

        Files(i) = Files(0) & "\" & Files(i)              '连接路径和文件名,组成文件数组

    Next i

 

代码不多,最后的文件列表保存在Files数组里。因为第一次使用CommonDialog控件打开多个文件,查找出多个文件的分割符号是ASCII码为0的字符费了你不少时间。你开始查了文档,没有得到信息;将FileName属性用Msgbox输出只有路径,在调试状态跟踪时是一个怪字符分割的;你开始想想应该是Tab或者回车之类的,然后使用这些字符用Split函数分割,没有成功;只好测试了,你将所有字符使用ASC函数输出,发现原来是ASCII码为0的字符。你想,微软的文档向来不错,为什么这个在帮助里没有呢?

后面的部分就简单了。

 

For i = 1 To UBound(Files) Step 1

    strFilename = Files(i)

       DoImport strFilename

Next i

 

把原来的宏修改后保存在DoImport这个过程里,传入文件名即可导入这个文件,循环导入所有文件就可以了。虽然程序功能复杂了,但代码似乎要有条理了。

4.2.    指定要导入的位置

聪明绝顶的MM很高兴,马上又举一反三,提出应该可以指定从第几行开始导入。你脑子转了一下,认为这个需求属于合理需求[③],不能不予理会。

给窗体加一个RefEdit,点击开始的区域后返回的将是一个引用位置的字符串,使用Range函数得到该区域的引用对象(Range对象),然后就可以得到其开始行数:

Range(Me.RefEdit1.Value).Row

重构一下DoImport这个过程,增加一个mRow参数,将导入的数据全部写到第mRow行。上面的调用过程就变成了:

 

dim mRow as long

mRow = Range(Me.RefEdit1.Value).Row

For i = 1 To UBound(Files) Step 1

    strFilename = Files(i)

       DoImport strFilename, mRow

       mRow = mRow + 1

Next i

 

你终于松了一口气,脸上又浮现出了贼贼的笑容。MM也答应要请你吃饭,不过你知道兑现的可能性不大,最后还可能是MM请客你掏钱,不过不要紧,程序员的最起码的风度和尊严还是要维护的。

4.3.    修改导入规则

你忽然觉得灵感一现,甚至想做一个可以导入各种格式文件的通用模块,然后作为加载宏发布,于是很多实验室都开始用你的程序,你开始狂收注册费,Gates也坐不住了,要把你的程序买了OEM在Excel里……

这时,呆在一旁的MM敲了一下你的头,把手舞足蹈的你拉回了现实。

5.       总结

晚上,你打开了日志,写道“替Girl friend MM解决问题一个”。

你想,今天的问题很简单,不过,解决的问题好像很管用,这大概就是所谓的VBA之道吧,以前似乎见过一个VB之道的帖子,你决定回头也写一个VBA之道。那么,第一条应该是:

录制宏,但要修改它!

 

 

(2004-11-24 凌晨)

[①] 这是很多程序员的通病,喜欢从轮子造起,你也一样。

[②] 你知道有一个Sendkeys语句,可以模拟键盘操作关闭这个对话框,不过解决主要问题先。

[③] 你认为合理需求应该满足以下两条定律:第一是可以实现,第二是客户提出的。

本文地址:http://com.8s8s.com/it/it42641.htm