Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using split function for a csv file with blank line in between
I used split to convert a csv file into a string array. Everything works
fine until I've a file with empty lines in between and when I run the split function, my program will give an error "Script out of range" as long as it hits the blank line. How can I overcome this? Is the empty line causing the problem ? My script is as below Open filename For Input As #1 x = 0 m = 1 z = a + b w = 0 For n = 1 To w Line Input #1, strline arrstring = Split(strline, ",") Worksheets("384-template").Range("B1").Offset(x + z, 0).Value = arrstring(1){Debug stops here} Worksheets("384-template").Range("C1").Offset(x + z, 0).Value = arrstring(2) Worksheets("384-template").Range("D1").Offset(x + z, 0).Value = arrstring(0) x = x + 48 m = m + 1 If m 8 Then m = 1 x = 0 z = z + 2 End If 'Debug.Print n, x, z, x + z, m Next n Close #1 Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using split function for a csv file with blank line in between
Test your arrstring to see if it is a valid array before attempting to work
with its elements: arrstring = Split(strline,",") If LBound(arrstring) <= UBound(arrstring) Then ' do you thing Else ' empty line read End If Note that this method won't work with arrays in general. It will work only with arrays that are set properly by the Split and a few other functions. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "kfng" wrote in message ... I used split to convert a csv file into a string array. Everything works fine until I've a file with empty lines in between and when I run the split function, my program will give an error "Script out of range" as long as it hits the blank line. How can I overcome this? Is the empty line causing the problem ? My script is as below Open filename For Input As #1 x = 0 m = 1 z = a + b w = 0 For n = 1 To w Line Input #1, strline arrstring = Split(strline, ",") Worksheets("384-template").Range("B1").Offset(x + z, 0).Value = arrstring(1){Debug stops here} Worksheets("384-template").Range("C1").Offset(x + z, 0).Value = arrstring(2) Worksheets("384-template").Range("D1").Offset(x + z, 0).Value = arrstring(0) x = x + 48 m = m + 1 If m 8 Then m = 1 x = 0 z = z + 2 End If 'Debug.Print n, x, z, x + z, m Next n Close #1 Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using split function for a csv file with blank line in between
Thanks a lot! It worked perrfectly fine!
"Chip Pearson" wrote: Test your arrstring to see if it is a valid array before attempting to work with its elements: arrstring = Split(strline,",") If LBound(arrstring) <= UBound(arrstring) Then ' do you thing Else ' empty line read End If Note that this method won't work with arrays in general. It will work only with arrays that are set properly by the Split and a few other functions. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "kfng" wrote in message ... I used split to convert a csv file into a string array. Everything works fine until I've a file with empty lines in between and when I run the split function, my program will give an error "Script out of range" as long as it hits the blank line. How can I overcome this? Is the empty line causing the problem ? My script is as below Open filename For Input As #1 x = 0 m = 1 z = a + b w = 0 For n = 1 To w Line Input #1, strline arrstring = Split(strline, ",") Worksheets("384-template").Range("B1").Offset(x + z, 0).Value = arrstring(1){Debug stops here} Worksheets("384-template").Range("C1").Offset(x + z, 0).Value = arrstring(2) Worksheets("384-template").Range("D1").Offset(x + z, 0).Value = arrstring(0) x = x + 48 m = m + 1 If m 8 Then m = 1 x = 0 z = z + 2 End If 'Debug.Print n, x, z, x + z, m Next n Close #1 Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to split data where line breaks are the delimiter | Excel Worksheet Functions | |||
HOW DO I CHANGE THE COLOR OF THE EXCEL SPLIT LINE | New Users to Excel | |||
How do I split the axis scale on a line graph | Excel Discussion (Misc queries) | |||
How do you add a blank line automatically after the Subtotal line | Excel Worksheet Functions | |||
Text File Import leaves a blank row after each line | Setting up and Configuration of Excel |