在一个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: none@none.com
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: none@none.com
希望转换数据到excel文件中,并在一行显示同一记录。
Order ID | PurchaseDate | ShippingService | BuyerName | BuyerE-mail |
# xxxxxxx | February 2, 2009 10 | Standard | John Doe | none@none.com |
# xxxxxxx | February 2, 2009 10 | Standard | John Doe | none@none.com |
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
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