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

Excel 技巧网

 找回密码
 注册

QQ登录

只需一步,快速开始

?#21482;?#21495;码,快捷登录

查看: 44269|回复: 37
打印 上一主题 下一主题

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

  [复制链接]
跳转到指定楼层
楼主
发表于 2012-6-28 23:35:17 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
  • 网站名称: Excel技巧网 | Excel专家栖息谷 | 微软中文技术社区合作伙伴
  • 署名作者: xyf2210
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


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

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

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

    然后,我们要处理成这个的格式:

    创建与导入文本文件.rar (172.99 KB, 下载次数: 534)


    评分

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

    查看全部评分

    分享到:  QQ好友和群QQ好友和群
    收藏收藏24
    2
     楼主| 发表于 2012-6-28 23:37:20 | 只看该作者
    (1)首先,我们可以一个,当年在VB里面,用?#21019;?#29702;文本文件的利器,open语句
    Open语句打开文件的语法为:
    Open 路径+文件名 For Input As #数字    '数?#24535;?#26159;打开的这个文件的别名
    之后,我们可以用Line Input语句,一行行的把数据导入,并进行处理,直至文本文件的尾端(EOF(1)),详见代码。这个方法是最常用的,大家尽量学透就是了。
    1. Sub Open方法()
    2.     Dim d, i, sr$, temp
    3.     Set d = CreateObject("scripting.dictionary")      '引用?#20540;?br />
    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)   '运行到文件的结?#27493;?#26463;
    9.             Line Input #1, sr            '按行读取数据
    10.             d(i) = Split(sr, ",")        '以逗号分割?#22336;?#20018;
    11.             i = i + 1
    12.         Loop
    13.         Close #1              '关闭文本文件
    14.         temp = Application.Transpose(d.Items)     '转置?#20540;?#30340;项
    15.         .Range("a1").Resize(d.Count, UBound(temp)) = Application.Transpose(temp)     '将数组写入单元格
    16.     End With
    17.     Set d = Nothing
    18. End Sub
    复制代码
    3
     楼主| 发表于 2012-6-28 23:38:41 | 只看该作者
    (2)相信,很多人学习VBA,都是从录制宏学起吧,呵呵,我们可以运用,excel的获取外部数据,导入文本数据的功能(如图)
    录制宏,进行修?#27169;?#26469;导入文本数据,我们可以发现,EXCEL其实是运用QueryTables这个对象来导入数据(QueryTables对象成员解析,详见F1帮助?#24471;鰨?#37324;面很详细,本文就不再复述)。首先运用add的方法,添加连接,然后运用QUERYTABLE对象的关于分隔符设置的属性(详细见F1里面,QueryTable 对象成员中,TEXT打头的几个属性),处理文本,导入数据。详?#22797;?#30721;如下:
    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
    复制代码
    4
     楼主| 发表于 2012-6-28 23:39:04 | 只看该作者
    本帖最后由 xyf2210 于 2012-6-28 23:41 编辑

    (3)其实,大家不知道有没有试过,我们其实可以用文件-打开-选择文本文件-打开,的方法,也可以导入文本文件(如图)

    一样,我们可以用录制宏的方式进行学习。我们观察代码,EXCEL其实是调用了Workbooks.OpenText方法,来导入文件(F1里面,对这个方面的解释,相当的详细,大家可以去看看,主要?#24425;?#19968;些属性的设置)。详细见如下代码解释:
    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
    复制代码
    5
     楼主| 发表于 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")           '引用?#20540;?br />
    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
    复制代码
    6
     楼主| 发表于 2012-6-28 23:44:58 | 只看该作者
    本帖最后由 xyf2210 于 2012-6-29 23:03 编辑

    (5)接下来是,ADO+SQL法,很多数据库的高手,?#19981;?#29992;SQL?#21019;?#29702;文本数据,这个方法比较灵活,可以先读入再分列,但是比?#32454;丛櫻?#32780;且ADO对象解析起来也可以写一本书了。大家?#24425;牽?#25226;以下代码,当做模板,进行修改使用。
    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.         '设置连接?#22336;?#20018;
    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)   '执行查询,并将结果输出到记?#25216;?#23545;象
    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              '导入记?#25216;?br />
    18.     End With
    19.     AdoRst.Close: adoconn.Close               '关闭数据库连接
    20. End Sub
    复制代码
    7
     楼主| 发表于 2012-6-28 23:45:58 | 只看该作者
    本帖最后由 xyf2210 于 2012-6-28 23:46 编辑

    (6)最后一种,来个偏门,仅作为补充学习,较?#36873;?#24403;没有WINDOW的时候,大家又是怎么读取文本文件的呢。呵呵,在DOS环境里面,可以用type语句来显示文本文件的数据。在VBA里面,我们可以借用WSH,来执行DOS语句,并将文本内容读入。先行声明,Windows95/98 中已经都可以使用长文件名/目录 (最长可以到255个字节),但是目前版本不支?#25351;丛?#30340;长文件名,可以利用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
    复制代码
    8
     楼主| 发表于 2012-6-28 23:47:28 | 只看该作者
    讲完了导入,那导出呢,我们怎么把数据从EXCEL导出到TXT文件呢。呵呵,其实很多都?#24039;?#38754;几种方法的反方向而?#36873;?br /> (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
    复制代码
    9
     楼主| 发表于 2012-6-28 23:51:01 | 只看该作者
    本帖最后由 xyf2210 于 2012-6-28 23:55 编辑

    (2)还是录制宏,录制,文件-另存为-文本文件的宏(如图)

    这个方法的好处就是,处理速度很快,但是就是,格?#22870;冉系?#19968;,较难个性化的保存其中的文本数据。
    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
    复制代码
    10
     楼主| 发表于 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方法写入一个指定的?#22336;?#20018;和换行符到文本文件中
    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是可选的,没有?#21019;?#24314;
    23.     '此例即追加
    24.     Set myfile = CreateObject("scripting.filesystemobject").OpenTextFile(ThisWorkbook.Path & "\工资表.txt", 8, True)
    25.     '将当前的数据读入数组
    26.     arr = Sheet2.Range("a1").CurrentRegion
    27.     '使用WriteLine方法写入一个指定的?#22336;?#20018;和换行符到文本文件中
    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技巧网的会员?#25945;?#38382;题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的?#26376;郟?#26412;站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单?#25442;?#20010;人未经允许,不得将其用于商业用?#23613;?
    若非原文作者,任何?#38382;?#30340;?#24039;?#19994;性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师

    Archiver|?#21482;?#29256;|Excel技巧网 ( 闽ICP备08107682号-2 ) | 闽公网安备 35020302032608号  

    GMT+8, 2019-9-10 05:08

    Powered by Discuz! X3.4

    © 2001-2017 Comsenz Inc.

    快速回复 返回顶部 返回列表
    足球规则 北京单场sp值开奖 极速快三计划能赚钱吗 可以炸金花的娱乐平台 福彩深圳风采走势图大星 3d试机号个位数口诀 3d杀号技巧 日赚50元的稳定项目 370老时时彩单式上传 中国福彩网我的积分 一分快三是不是官方彩票 河南快赢481开奖视频结果 彩票甘肃快3开奖查询 最早一码中特资料 中国竞彩网半全场 福彩3d投注技巧