設為首頁收藏本站|繁體中文

Excel 技巧網

 找回密碼
 注冊

QQ登錄

只需一步,快速開始

手機號碼,快捷登錄

查看: 46711|回復: 41
打印 上一主題 下一主題

[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, 下載次數: 548)


    評分

    參與人數 7魅力值 +32 收起 理由
    wangg913 + 2 很實用
    wjc2090742 + 5 技法嫻熟:)
    YESS95 + 5 二師兄,好V5
    windimi007 + 5 魔王v5******!
    千年一夢遙 + 5 技法嫻熟:)

    查看全部評分

    分享到:  QQ好友和群QQ好友和群
    收藏收藏25
    2
     樓主| 發表于 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
    復制代碼
    3
     樓主| 發表于 2012-6-28 23:38:41 | 只看該作者
    (2)相信,很多人學習VBA,都是從錄制宏學起吧,呵呵,我們可以運用,excel的獲取外部數據,導入文本數據的功能(如圖)
    錄制宏,進行修改,來導入文本數據,我們可以發現,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
    復制代碼
    4
     樓主| 發表于 2012-6-28 23:39:04 | 只看該作者
    本帖最后由 xyf2210 于 2012-6-28 23:41 編輯

    (3)其實,大家不知道有沒有試過,我們其實可以用文件-打開-選擇文本文件-打開,的方法,也可以導入文本文件(如圖)

    一樣,我們可以用錄制宏的方式進行學習。我們觀察代碼,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
    復制代碼
    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")           '引用字典
    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法,很多數據庫的高手,喜歡用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
    復制代碼
    7
     樓主| 發表于 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
    復制代碼
    8
     樓主| 發表于 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
    復制代碼
    9
     樓主| 發表于 2012-6-28 23:51:01 | 只看該作者
    本帖最后由 xyf2210 于 2012-6-28 23:55 編輯

    (2)還是錄制宏,錄制,文件-另存為-文本文件的宏(如圖)

    這個方法的好處就是,處理速度很快,但是就是,格式比較單一,較難個性化的保存其中的文本數據。
    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方法寫入一個指定的字符串和換行符到文本文件中
    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
    復制代碼
    您需要登錄后才可以回帖 登錄 | 注冊

    本版積分規則

    Archiver|手機版|Excel技巧網 ( 閩ICP備08107682號-2

    GMT+8, 2020-1-20 08:20

    Powered by Discuz! X3.4

    © 2001-2017 Comsenz Inc.

    快速回復 返回頂部 返回列表
    国际冠军杯