-
Sep 12th, 2019, 04:54 AM
#1
Thread Starter
New Member
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
This is excel file output.xlsx
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.
-
Sep 12th, 2019, 09:34 AM
#2
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
-
Sep 13th, 2019, 06:42 AM
#3
Thread Starter
New Member
Re: VBScript Importing .txt file to .xlsx file
Originally Posted by OptionBase1
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|