设为首页收藏本站|繁體中文

Excel 技巧网

 找回密码
 注册

QQ登录

只需一步,快速开始

手机号码,快捷登录

查看: 37923|回复: 36

[Excel VBA] VBA中TXT文件和EXCEL进行数据交互的几种方法简析

  [复制链接]
发表于 2012-6-28 23:35:17 | 显示全部楼层 |阅读模式
  • 署名作者: xyf2210
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


  • 免费注册成为本站会员,享用更多功能,结识更多Office办公高手!

    您需要 登录 才可以下载或查看,没有帐号?注册

    x
    由于工作需求,需要频繁的将业务系统导出的TXT文件数据,导入到EXCEL中,经过数据整理后,再导出到txt文本中。经过查找学习,总结了一下,EXCEL和TXT文本文件的数据交互,在VBA中,主要有以下几种方式:
    按导入和导出分开来进行解析,如有一些不恰当的解释,请大家指正:
    (一)导入文本文件数据到EXCEL中:
    以如图所示文本文件为例:
    文本文件.jpg
    然后,我们要处理成这个的格式:
    需求.jpg
    创建与导入文本文件.rar (172.99 KB, 下载次数: 508)

    评分

    参与人数 7魅力值 +32 收起 理由
    wangg913 + 2 很实用
    wjc2090742 + 5 技法娴熟:)
    YESS95 + 5 二师兄,好V5
    windimi007 + 5 魔王v5******!
    千年一梦遥 + 5 技法娴熟:)

    查看全部评分

     楼主| 发表于 2012-6-28 23:37:20 | 显示全部楼层
    (1)首先,我们可以一个,当年在VB里面,用来处理文本文件的利器,open语句
    Open语句打开文件的语法为:
    Open 路径+文件名 For Input As #数字    '数字就是打开的这个文件的别名
    之后,我们可以用Line Input语句,一行行的把数据导入,并进行处理,直至文本文件的尾端(EOF(1)),详见代码。这个方法是最常用的,大家尽量学透就是了。
    1. Sub Open方法()
    2.     Dim d, i, sr$, temp
    3.     Set d = CreateObject("scripting.dictionary")      '引用字典
    4.     With Sheet1
    5.         .UsedRange.ClearContents            '清除原有的数据
    6.         i = 1
    7.         Open ThisWorkbook.Path & "\工资表.txt" For Input As #1    '使用open语句输入
    8.         Do While Not EOF(1)   '运行到文件的结尾结束
    9.             Line Input #1, sr            '按行读取数据
    10.             d(i) = Split(sr, ",")        '以逗号分割字符串
    11.             i = i + 1
    12.         Loop
    13.         Close #1              '关闭文本文件
    14.         temp = Application.Transpose(d.Items)     '转置字典的项
    15.         .Range("a1").Resize(d.Count, UBound(temp)) = Application.Transpose(temp)     '将数组写入单元格
    16.     End With
    17.     Set d = Nothing
    18. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:38:41 | 显示全部楼层
    (2)相信,很多人学习VBA,都是从录制宏学起吧,呵呵,我们可以运用,excel的获取外部数据,导入文本数据的功能(如图) 1.gif
    录制宏,进行修改,来导入文本数据,我们可以发现,EXCEL其实是运用QueryTables这个对象来导入数据(QueryTables对象成员解析,详见F1帮助说明,里面很详细,本文就不再复述)。首先运用add的方法,添加连接,然后运用QUERYTABLE对象的关于分隔符设置的属性(详细见F1里面,QueryTable 对象成员中,TEXT打头的几个属性),处理文本,导入数据。详细代码如下:
    1. Sub 查询表方法()
    2.     With Sheet1
    3.         .UsedRange.ClearContents        '清除原有的数据
    4.         With .QueryTables.Add(Connection:="TEXT;" & ThisWorkbook.Path & "\工资表.txt", Destination:=Range("A1"))    '新建查询表
    5.             .TextFileCommaDelimiter = True         '以逗号作为分隔符
    6.             .Refresh      '更新外部数据区域
    7.         End With
    8.     End With
    9. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:39:04 | 显示全部楼层
    本帖最后由 xyf2210 于 2012-6-28 23:41 编辑

    (3)其实,大家不知道有没有试过,我们其实可以用文件-打开-选择文本文件-打开,的方法,也可以导入文本文件(如图)
    2.gif
    一样,我们可以用录制宏的方式进行学习。我们观察代码,EXCEL其实是调用了Workbooks.OpenText方法,来导入文件(F1里面,对这个方面的解释,相当的详细,大家可以去看看,主要也是一些属性的设置)。详细见如下代码解释:
    1. Sub opentext方法()
    2.     Dim arr
    3.     With Sheet1
    4.         .UsedRange.ClearContents           '清除原有的数据
    5.         Workbooks.OpenText FileName:=ThisWorkbook.Path & "\工资表.txt", DataType:=xlDelimited, Startrow:=1, comma:=True
    6.         'startrow:=1是文本分列处理的起始行号
    7.         'DataType:=xlDelimited是指示文件由分隔符分隔
    8.         'comma:=true是指以逗号作为分隔符
    9.         arr = ActiveWorkbook.Sheets("工资表").UsedRange
    10.         ActiveWorkbook.Close False
    11.         .Range("a1").Resize(UBound(arr), UBound(arr, 2)) = arr
    12.     End With
    13. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:42:16 | 显示全部楼层
    本帖最后由 xyf2210 于 2012-6-29 00:06 编辑

    (4)我们还可以调用FSO(FileSystemObject)方法来导入文本文件。FSO对象,大家一般都是用于文件和文件夹的处理。其实它也支持对文本流对象的处理的。FSO对象博大精深,详细的解释请参考MSDN,由于这个对象较难,初学者,可以把以下代码,作为模板,进行修改使用。
    1. Sub fso方法()
    2.     Dim d, i, sr$, temp, myfile As Object
    3.     With Sheet1
    4.         .UsedRange.ClearContents
    5.         Set d = CreateObject("scripting.dictionary")           '引用字典
    6.         Set myfile = CreateObject("scripting.filesystemobject").OpenTextFile(ThisWorkbook.Path & "\工资表.txt")
    7.         '引用FSO需要单击工具-引用-选中Microsoft Scripting Runtime
    8.         'fso的opentextfile方法文件并返回一个TextStream对象
    9.         i = 1
    10.         Do While Not myfile.AtEndOfStream           '运行到myfile的结尾
    11.             sr = myfile.ReadLine                     '读取整行
    12.             d(i) = Split(sr, ",")
    13.             i = i + 1
    14.         Loop
    15.         myfile.Close
    16.         temp = Application.Transpose(d.Items)
    17.         .Range("a1").Resize(d.Count, UBound(temp)) = Application.Transpose(temp)
    18.     End With
    19.     Set d = Nothing: Set myfile = Nothing
    20. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:44:58 | 显示全部楼层
    本帖最后由 xyf2210 于 2012-6-29 23:03 编辑

    (5)接下来是,ADO+SQL法,很多数据库的高手,喜欢用SQL来处理文本数据,这个方法比较灵活,可以先读入再分列,但是比较复杂,而且ADO对象解析起来也可以写一本书了。大家也是,把以下代码,当做模板,进行修改使用。
    1. Sub ado方法()
    2.     Dim adoconn As Object, strSQL As String, strConn As String, AdoRst As Object
    3.     Dim i, txt
    4.     Set adoconn = CreateObject("adodb.connection")
    5.     txt = ThisWorkbook.Path & "\工资表.txt"
    6.     With Sheet1
    7.         .UsedRange.ClearContents           '清除原有的数据
    8.         '设置连接字符串
    9.         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    10.                   "Data Source=" & ThisWorkbook.Path & ";Extended Properties=""Text;HDR=YES"""
    11.         strSQL = "select * from 工资表.txt"        '设置SQL查询语句
    12.         adoconn.Open strConn                            '打开数据库连接
    13.         Set AdoRst = adoconn.Execute(strSQL)   '执行查询,并将结果输出到记录集对象
    14.         For i = 0 To AdoRst.Fields.Count - 1
    15.             .Cells(1, i + 1) = AdoRst.Fields(i).Name              '填写标题
    16.         Next
    17.         .Range("A2").CopyFromRecordset AdoRst              '导入记录集
    18.     End With
    19.     AdoRst.Close: adoconn.Close               '关闭数据库连接
    20. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:45:58 | 显示全部楼层
    本帖最后由 xyf2210 于 2012-6-28 23:46 编辑

    (6)最后一种,来个偏门,仅作为补充学习,较难。当没有WINDOW的时候,大家又是怎么读取文本文件的呢。呵呵,在DOS环境里面,可以用type语句来显示文本文件的数据。在VBA里面,我们可以借用WSH,来执行DOS语句,并将文本内容读入。先行声明,Windows95/98 中已经都可以使用长文件名/目录 (最长可以到255个字节),但是目前版本不支持复杂的长文件名,可以利用API函数取得短文件名,再利用短文件名读入文本文件。
    1. Sub dos用法()
    2.     Dim StrCmd As String, StrPath As String * 256, LngRes As Integer
    3.     Dim i As Integer, str As String, arr1, arr2
    4.     LngRes = GetShortPathName(ThisWorkbook.Path & "\工资表.txt", StrPath, 256)
    5.     Sheet1.UsedRange.ClearContents
    6.     str = CreateObject("wscript.shell").exec(Environ("comspec") & " /c""type " & StrPath).StdOut.ReadAll
    7.     arr1 = Split(str, vbCrLf)
    8.     ReDim arr2(1 To UBound(arr1), 1 To 3)
    9.     For i = 1 To UBound(arr1)
    10.         arr2(i, 1) = Split(arr1(i - 1), ",")(0)
    11.         arr2(i, 2) = Split(arr1(i - 1), ",")(1)
    12.         arr2(i, 3) = Split(arr1(i - 1), ",")(2)
    13.     Next i
    14.     [a1].Resize(i - 1, 3) = arr2
    15. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:47:28 | 显示全部楼层
    讲完了导入,那导出呢,我们怎么把数据从EXCEL导出到TXT文件呢。呵呵,其实很多都是上面几种方法的反方向而已。
    (1)第一种,还是,OPEN语句
    Open 路径+文件名 For Output As #数字 ‘数字为文件的别名
    用OPEN语句创建文本文件,就可以使用PRINT语句,一行行的输出文本内容了。
    1. Sub test()
    2.     Dim file As String, arr, i
    3.     '定义文本文件的名称
    4.     file = ThisWorkbook.Path & "\工资表.txt"
    5.     '判断是否存在同名文本文件,存在先行删除
    6.     If Dir(file) <> "" Then Kill file
    7.     '将当前的数据读入数组
    8.     arr = Sheet2.Range("a1").CurrentRegion
    9.     '使用print语句将数组中所有数据写入文本文件
    10.     Open file For Output As #1
    11.     For i = 1 To UBound(arr)
    12.         Print #1, Join(Application.Index(arr, i), ",")
    13.     Next
    14.     '关闭文本文件
    15.     Close #1
    16. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:51:01 | 显示全部楼层
    本帖最后由 xyf2210 于 2012-6-28 23:55 编辑

    (2)还是录制宏,录制,文件-另存为-文本文件的宏(如图)
    3.gif
    这个方法的好处就是,处理速度很快,但是就是,格式比较单一,较难个性化的保存其中的文本数据。
    1. Sub 另存为文本文件()
    2.     Dim file As String
    3.     '定义文本文件的名称
    4.     file = ThisWorkbook.Path & "\工资表.txt"
    5.     '判断是否存在同名文本文件,存在先行删除
    6.     If Dir(file) <> "" Then Kill file
    7.     '复制工作表另存为文本文件,xlCSV即保存为文本文件
    8.     Sheet2.Copy
    9.     ActiveWorkbook.SaveAs FileName:=file, FileFormat:=xlCSV
    10.     ActiveWorkbook.Close False
    11. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2012-6-28 23:51:43 | 显示全部楼层
    本帖最后由 xyf2210 于 2012-6-28 23:57 编辑

    (3)第三种,调用FSO对象,去创建和保存文本数据。这个方法一样,比较难,作为拓展了解吧。可以使用createtextfile和OpenTextFile进行操作。
    1. Sub createtextfile()
    2.     Dim arr, i, myfile As Object
    3.     '创建FSO对象利用Createtextfile方法创建文本文件
    4.     'object.CreateTextFile(filename[, overwrite[, unicode]])
    5.     '参数overwrite是可选的,表示是否覆盖已存在文件,如果省略,则已存在文件不能覆盖,这里面这只True,表示覆盖
    6.     Set myfile = CreateObject("scripting.filesystemobject").createtextfile(ThisWorkbook.Path & "\工资表.txt", True)
    7.     '将当前的数据读入数组
    8.     arr = Sheet2.Range("a1").CurrentRegion
    9.     '使用WriteLine方法写入一个指定的字符串和换行符到文本文件中
    10.     For i = 1 To UBound(arr)
    11.         myfile.WriteLine Join(Application.Index(arr, i), ",")
    12.     Next
    13.     '关闭对象
    14.     myfile.Close
    15.     Set myfile = Nothing
    16. End Sub
    17. Sub OpenTextFile()
    18.     Dim arr, i, myfile As Object
    19.     '创建FSO对象利用OpenTextFile方法创建文本文件
    20.     'object.OpenTextFile(filename[, iomode[, create[, format]]])
    21.     '参数iomode是可选的,1 forreading,打开只读,不能操作;8 forappending,打开并写入尾部
    22.     '参数create是可选的,没有即创建
    23.     '此例即追加
    24.     Set myfile = CreateObject("scripting.filesystemobject").OpenTextFile(ThisWorkbook.Path & "\工资表.txt", 8, True)
    25.     '将当前的数据读入数组
    26.     arr = Sheet2.Range("a1").CurrentRegion
    27.     '使用WriteLine方法写入一个指定的字符串和换行符到文本文件中
    28.     For i = 1 To UBound(arr)
    29.         myfile.WriteLine Join(Application.Index(arr, i), ",")
    30.     Next
    31.     '关闭对象
    32.     myfile.Close
    33.     Set myfile = Nothing: Erase arr
    34. End Sub
    复制代码
    回复 支持 反对

    使用道具 举报

    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    Excel技巧网的会员探讨问题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的言论,本站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单位或个人未经允许,不得将其用于商业用途。
    若非原文作者,任何形式的非商业性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师

    Archiver|手机版|Excel技巧网 ( 闽ICP备08107682号-2 ) | 闽公网安备 35020302032608号  

    GMT+8, 2018-9-22 07:24

    Powered by Discuz! X3.3

    © 2001-2017 Comsenz Inc.

    快速回复 返回顶部 返回列表