Results 1 to 3 of 3

Thread: VBScript Importing .txt file to .xlsx file

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    2

    VBScript Importing .txt file to .xlsx file

    Hello there, I need your help.

    I use this VBScript code for trying import on excel file output.xlsx the text file input.txt

    This is text file input.txt

    Name:  2LPbb.jpg
Views: 2039
Size:  72.8 KB

    This is excel file output.xlsx

    Name:  pG6E6.jpg
Views: 1757
Size:  12.4 KB

    This code don't have error but it is an infinite loop.
    The text file contains 34 rows ... but import infinite rows with only first row of input.txt file

    How to do resolve this ?

    Thanks in advance for any help, really appreciated.

    Vinc

    Code:
      ExcelFilePath = "D:\xlsx\output.xlsx"
    
      Set objExcel = CreateObject("Excel.Application")
      objExcel.visible = True
      Set objWB = objExcel.Workbooks.Open(ExcelFilePath)
      Set SheetObject = objWB.Worksheets("Sheet1")
    
      TextFile = "D:\txt\input.txt"
    
      Set TextRead = objFSO.OpenTextFile(TextFile, ForReading)
    
      row = 2
    
      With SheetObject
    
      Do Until TextRead.AtEndOfStream
          strLine = Trim(TextRead.ReadLine)
    
          If (strLine <> "") Then             
              arrValues = Split(strLine, "|")
              For irow = 2 to Ubound(arrValues) + 1               
                  For icol = 1 to Ubound(arrValues) + 1
                     SheetObject.Cells(irow, icol).value = arrValues(icol-1)
                  Next
               Next
          End If
      Loop  
    
      End With  
      
      objWB.Save
      objWB.Close
      objExcel.Quit
    Last edited by Vincenzo8937; Sep 12th, 2019 at 06:53 AM.

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: VBScript Importing .txt file to .xlsx file

    Your "For irow" statement inside the Do-Loop is the cause of the problem. It is creating a new row of data for every element in the array for each line read, which presumably is wrong. It should be creating a new row of data only once per line read.

    That being the case, this may work better. I haven't tried the code, but it should be closer to right.

    Code:
      ExcelFilePath = "D:\xlsx\output.xlsx"
    
      Set objExcel = CreateObject("Excel.Application")
      objExcel.visible = True
      Set objWB = objExcel.Workbooks.Open(ExcelFilePath)
      Set SheetObject = objWB.Worksheets("Sheet1")
    
      TextFile = "D:\txt\input.txt"
    
      Set TextRead = objFSO.OpenTextFile(TextFile, ForReading)
    
      row = 2
    
      With SheetObject
    
      Do Until TextRead.AtEndOfStream
          strLine = Trim(TextRead.ReadLine)
    
          If (strLine <> "") Then             
              arrValues = Split(strLine, "|")
              For icol = 1 to Ubound(arrValues) + 1
                 SheetObject.Cells(row, icol).value = arrValues(icol-1)
              Next
          End If
          row = row + 1
      Loop  
    
      End With

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    2

    Re: VBScript Importing .txt file to .xlsx file

    Quote Originally Posted by OptionBase1 View Post
    Your "For irow" statement inside the Do-Loop is the cause of the problem. It is creating a new row of data for every element in the array for each line read, which presumably is wrong. It should be creating a new row of data only once per line read.

    That being the case, this may work better. I haven't tried the code, but it should be closer to right.

    Code:
      ExcelFilePath = "D:\xlsx\output.xlsx"
    
      Set objExcel = CreateObject("Excel.Application")
      objExcel.visible = True
      Set objWB = objExcel.Workbooks.Open(ExcelFilePath)
      Set SheetObject = objWB.Worksheets("Sheet1")
    
      TextFile = "D:\txt\input.txt"
    
      Set TextRead = objFSO.OpenTextFile(TextFile, ForReading)
    
      row = 2
    
      With SheetObject
    
      Do Until TextRead.AtEndOfStream
          strLine = Trim(TextRead.ReadLine)
    
          If (strLine <> "") Then             
              arrValues = Split(strLine, "|")
              For icol = 1 to Ubound(arrValues) + 1
                 SheetObject.Cells(row, icol).value = arrValues(icol-1)
              Next
          End If
          row = row + 1
      Loop  
    
      End With
    Many thanks !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width