批处理之家 - Powered by Discuz! Board

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

作者: iq301 时间: 2014-6-9 23:55 标题: [已解决]VBS如何将txt文本数据按指定样式导入/生成Excel表格？

本帖最后由 pcl_test 于 2016-7-27 20:50 编辑

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 这句提示错误，语句未结束。求解

作者: CrLf 时间: 2014-6-10 00:15

本帖最后由 CrLf 于 2014-6-10 00:16 编辑

vbs 和 vba 不一样，不能直接套用，可以参考下别人怎么写：
http://blog.csdn.net/chentaocba/article/details/7907800

作者: iq301 时间: 2014-6-11 00:38

回复 2# CrLf

谢谢分享。

作者: pcl_test 时间: 2016-7-27 21:58

Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True 
Set objBook= ExcelApp.Workbooks.Add
str = split("Order ID|Purchase Date|Shipping Service|Buyer Name|Buyer E-mail", "|")
ExcelApp.WorkSheets(1).Activate
For i=0 to Ubound(str)
    ExcelApp.Cells(1, i+1) = str(i)
Next

i=1
Set f = fso.OpenTextFile("文本.txt", 1)
Do While f.AtEndOfStream <> true
    line = f.ReadLine
    If InStr(line, str(0)) >0 Then i=i+1:ExcelApp.Cells(i, 1) = split(line, ":")(1)
    If InStr(line, str(1)) >0 Then ExcelApp.Cells(i, 2) = split(line, ":")(1)
    If InStr(line, str(2)) >0 Then ExcelApp.Cells(i, 3) = split(line, ":")(1)
    If InStr(line, str(3)) >0 Then ExcelApp.Cells(i, 4) = split(line, ":")(1)
    If InStr(line, str(4)) >0 Then ExcelApp.Cells(i, 5) = split(line, ":")(1)
Loop

objBook.SaveAs(fso.GetFolder(".").Path&"\新建.xls")
objBook.Close
ExcelApp.Quit
复制代码

作者: zhangop9 时间: 2020-11-25 12:16

留个记号，答题

欢迎光临批处理之家 (http://bbs.bathome.net/)