激活一个Result工作簿,采用for循环将选进的文本文件导入excel,( 不考虑文本文件个数超出excel限制的情况,后面我有判断),下面的代码假如不加 Range("P4:P447").ClearContents则运行错误,加上发现文件都导入了P4这一列了,这是为什么?
Worksheets("Result").Activate
For lngCount = 1 To .SelectedItems.Count
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & .SelectedItems(lngCount), Destination:=Range("P4:IV447"))
.TextFileStartRow = 3
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9)
Range("P4:P447").ClearContents
.Refresh BackgroundQuery:=False
End With 问题补充:Worksheets("Result").Activate
' Connection:="TEXT;" & strFileName, Destination:=Selection
For lngCount = 1 To .SelectedItems.Count
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & .SelectedItems(lngCount), Destination:=Range("P4:IV447"))
.TextFileStartRow = 3
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9)
Range("P4:P447").ClearContents
.Refresh BackgroundQuery:=False
End With
'如果lngCount大于240,跳转到dk处,开始导入240之后的数据 If lngCount > 200 Then
GoTo dk
End If
Next lngCount
在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提醒你,这个她也能做到,但怎么样把所有的数据文件都导入进来。你看看她带来的文件,文件名是“”、“”、“”、“”之类,大概是时间加序列吧。
你想,嗯,写一个循环就可以了,你打开了刚才录制的宏,检查了一下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