[问题求助] [已解决]VBS如何将txt文本数据按指定样式导入/生成Excel表格？

iq301

EXCEL 读取txt文本数据  

在一个txt文本文件中存有一下格式数据

Your Merchant Order ID: # xxxxxxx
Purchase Date: February 2, 2009 10:17:30 AM PST
Shipping Service: Standard
Buyer Name: John Doe
Buyer E-mail: [email]none@none.com[/email]

Your Merchant Order ID: # xxxxxxx
Purchase Date: February 2, 2009 10:17:30 AM PST
Shipping Service: Standard
Buyer Name: John Doe
Buyer E-mail: [email]none@none.com[/email]
希望转换数据到excel文件中，并在一行显示同一记录。

Order ID        PurchaseDate        ShippingService        BuyerName        BuyerE-mail
# xxxxxxx        February 2, 2009 10        Standard        John Doe        [email]none@none.com[/email]
# xxxxxxx        February 2, 2009 10        Standard        John Doe        [email]none@none.com[/email]

Sub DataRead()
  Set fs = CreateObject("Scripting.FileSystemObject")
  Open ActiveWorkbook.Path & "\" & "ok.txt" For Input As #1
  i = 2
  Do While Not EOF(1)
        Line Input #1, txt
        Dim a
        a = Split(txt, ":")
        If InStr(txt, "Order ID") > 0 Then
                Cells(i, 1) = Trim(a(1))
        ElseIf InStr(txt, "Purchase Date") > 0 Then
                Cells(i, 2) = Trim(a(1))
        ElseIf InStr(txt, "Shipping Service") > 0 Then
                Cells(i, 3) = Trim(a(1))
        ElseIf InStr(txt, "Buyer Name") > 0 Then
                Cells(i, 4) = Trim(a(1))
        ElseIf InStr(txt, "Buyer E-mail") > 0 Then
                Cells(i, 5) = Trim(a(1))
                i = i + 1
        End If
  Loop
  Close #1
  End Sub
复制代码

上面代码，编译时，  Open ActiveWorkbook.Path & "\" & "ok.txt" For Input As #1 这句提示错误，语句未结束。求解

iq301

回复 2# CrLf


    谢谢分享。