如何将没有分隔符的.txt文件转换为Excel-Howtoconvert.txtfilewithnodelimiterintoExcel

2  

1. Open the worksheet.

   打开工作表。

2. Loop the data writing it to the worksheet instead of storing it like you were.

   循环数据将其写入工作表而不是像您一样存储它。

3. Save and close

   保存并关闭

Something like this.

像这样的东西。

Dim objFSO
Dim TextFile
Dim TextRead
Dim Line, Line1, Line2, Line3
Dim Count

Const ForReading = 1 'Constant declared so that can be used throughout the script

'Name of the text file that need to be convert
TextFile = "C:\Documents and Settings\Administrator\Desktop\2_12_2015\HRILOANDIC20170601.txt"

'Create File system object
set objFSO = CreateObject("Scripting.FileSystemObject")

'set the text file to read and open it in read-only mode
set TextRead = objFSO.OpenTextFile(TextFile,ForReading)

'Open the spreadsheet using the excel application object
ExcelFilePath = "C:\Documents and Settings\Administrator\Desktop\2_12_2015\Output.xlsx"
Set objExcel = CreateObject("Excel.Application")
Set SheetObject = ExcelObject.ActiveWorkbook.Worksheets(1)

'Change from storing the data in the below variables to writing them to the worksheet.

'Create yourself a counter for the worksheet row.
Dim lRow as long
lRow = 1

Do Until TextRead.AtEndOfStream
    Line = TextRead.ReadLine

    If Left(Line, 1) = "H" Then
        SheetObject.Range("A" & lRow).Value = Mid(Line, 1, 1)
        SheetObject.Range("B" & lRow).Value = Mid(Line, 2, 9)
        SheetObject.Range("C" & lRow).Value = Mid(Line, 11, 19)
        SheetObject.Range("D" & lRow).Value = Mid(Line, 30, 1)
        SheetObject.Range("E" & lRow).Value = Mid(Line, 31, 8)
        SheetObject.Range("F" & lRow).Value = Mid(Line, 39, 9)
        SheetObject.Range("G" & lRow).Value = Mid(Line, 48, 17)
        SheetObject.Range("H" & lRow).Value = Mid(Line, 65, 2)
        SheetObject.Range("I" & lRow).Value = Mid(Line, 67, 334)

    ElseIf Left(Line, 1) = "D" Then
        SheetObject.Range("A" & lRow).Value = Mid(Line, 1, 1)
        SheetObject.Range("B" & lRow).Value = Mid(Line, 2, 9)
        SheetObject.Range("C" & lRow).Value = Mid(Line, 11, 19)
        SheetObject.Range("D" & lRow).Value = Mid(Line, 30, 10)
        SheetObject.Range("E" & lRow).Value = Mid(Line, 40, 1)
        SheetObject.Range("F" & lRow).Value = Mid(Line, 41, 8)
        SheetObject.Range("G" & lRow).Value = Mid(Line, 49, 19)
        SheetObject.Range("H" & lRow).Value = Mid(Line, 68, 1)
        SheetObject.Range("I" & lRow).Value = Mid(Line, 69, 17)
        SheetObject.Range("J" & lRow).Value = Mid(Line, 86, 10)
        SheetObject.Range("K" & lRow).Value = Mid(Line, 96, 40)
        SheetObject.Range("L" & lRow).Value = Mid(Line, 136, 40)
        SheetObject.Range("M" & lRow).Value  = Mid(Line, 176, 3)
        SheetObject.Range("N" & lRow).Value = Mid(Line, 179, 200)
        SheetObject.Range("O" & lRow).Value  = Mid(Line, 379, 1)
        SheetObject.Range("P" & lRow).Value  = Mid(Line, 380, 19)
        SheetObject.Range("Q" & lRow).Value = Mid(Line, 399, 5)

    ElseIf Left(Line, 1) = "T" Then
        SheetObject.Range("A" & lRow).Value = Mid(Line, 1, 1)
        SheetObject.Range("B" & lRow).Value = Mid(Line, 2, 9)
        SheetObject.Range("C" & lRow).Value  = Mid(Line, 30, 9)
        SheetObject.Range("D" & lRow).Value  = Mid(Line, 39, 17)
        SheetObject.Range("E" & lRow).Value = Mid(Line, 56, 345)
    Else
        'Error Handling
    End If

    '  ************************ Don't overlook that i added this! **********
    'Increment the counter
    lRow = lRow + 1

Loop

'Then save and quit.

'Save and quit
ExcelObject.ActiveWorkbook.Save
ExcelObject.ActiveWorkbook.Close
ExcelObject.Application.Quit

1  

I have modified your code to write directly to the Excel workbook. I did not finish it completely, but gave you enough to modify the rest on your own. I also made comments as to where you had some syntax and general understanding errors. Make any column and row assignment adjustments as need be.

我已修改您的代码以直接写入Excel工作簿。我没有完全完成它,但给了你足够的修改其余的自己。我还评论了你在哪里有一些语法和一般理解错误。根据需要进行任何列和行分配调整。

Dim objFSO
Dim TextFile
Dim TextRead
Dim Line, Line1, Line2, Line3
Dim Count

'Open the spreadsheet using the excel application object
ExcelFilePath = "C:\Documents and Settings\Administrator\Desktop\2_12_2015\Output.xlsx"

Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(ExcelFilePath) ' have to open workbook 
Set SheetObject = objWB.Worksheets(1) 'worksheets are a member of workbooks, not the Excel Application

'open the text file
Const ForReading = 1 'Constant declared so that can be used throughout the script

'Name of the text file that need to be convert
TextFile = "C:\Documents and Settings\Administrator\Desktop\2_12_2015\HRILOANDIC20170601.txt"

'Create File system object
set objFSO = CreateObject("Scripting.FileSystemObject")

'set the text file to read and open it in read-only mode
set TextRead = objFSO.OpenTextFile(TextFile,ForReading)

i = 1 'to set row number for Excel paste

Do Until TextRead.AtEndOfStream

    Line = TextRead.ReadLine

    If Left(Line, 1) = "H" Then
        SheetObject.Cells(i, 1).Value = Mid(Line, 1, 1) 'HeaderRecordType to column A
        SheetObject.Cells(i, 2).Value = Mid(Line, 2, 9) 'ValueHeaderSequenceNo to column b
        SheetObject.Cells(i, 3).Value = Mid(Line, 11, 19) 'HeaderContractNo to column C

        '... keep going with your code

    Else
        'Error Handling
    End If

    i = i + 1 'to move down the Excel row to paste for each line in the text file
Loop

'Save and quit
objWB.Save
objWB.Close
objExcel.Quit