Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 26th 19, 11:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 64
Default Error during file read?

Gary and Claus, this is the problem that I presented earlier. Can anyone identify why the first function works perfectly, but the second function fails? The second function reads data from "C:\data.txt", which only contains "a123 Go now 123".

The output is supposed to be "Go now 123", but only the first function works. Both functions are essentially the same, except that the second one reads data from a file.

Is it possible that loading a file string into a variant type causes problems?

'-------------------------------------
Sub ThisWorks()

Dim sline As String

sline = "a123 Go now 123"

sline = Application.Substitute(sline, " ", "*", 1)
varTmp = Split(sline, "*")

MsgBox Trim(varTmp(1)) ' It works! It returns "Go now 123"

End Sub


'-------------------------------------
Sub ThisFails()
Dim n As Integer, i As Integer, RowNmbr As Integer
Dim varOut() As Variant
Dim sText As String
Dim sPath As String
Dim sline As String

sPath = "C:\data.txt"

'Open file for read
Open sPath For Input As #1

Do Until EOF(1)
RowNmbr = RowNmbr + 1
Line Input #1, sText
If (Len(sText) = 0) Then
GoTo NFR_ENDLOOP
End If
If StrChk(sText) = True Then
ReDim Preserve varOut(i)
varOut(i) = sText 'RowNmbr
i = i + 1
End If
NFR_ENDLOOP:
Loop
Close #1

sline = varOut(0) ' Read the first line: "a123 Go now 123"

sline = Application.Substitute(sline, " ", "*", 1)
varTmp = Split(sline, "*")

MsgBox Trim(varTmp(1)) ' Fails to output "Go now 123"

End Sub


  #2   Report Post  
Old May 26th 19, 10:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,084
Default Error during file read?

Gary and Claus, this is the problem that I presented earlier. Can anyone
identify why the first function works perfectly, but the second function
fails? The second function reads data from "C:\data.txt", which only
contains "a123 Go now 123".

The output is supposed to be "Go now 123", but only the first function works.
Both functions are essentially the same, except that the second one reads
data from a file.

Is it possible that loading a file string into a variant type causes
problems?

'-------------------------------------
Sub ThisWorks()

Dim sline As String

sline = "a123 Go now 123"

sline = Application.Substitute(sline, " ", "*", 1)
varTmp = Split(sline, "*")

MsgBox Trim(varTmp(1)) ' It works! It returns "Go now 123"


No.., it returns " Go now 123" BECAUSE sline has 4 spaces between "a123"
and "Go now 122" and your formula only replaces 1 of those spaces, leaving the
other 3 part of the 2nd array element.

End Sub


'-------------------------------------
Sub ThisFails()
Dim n As Integer, i As Integer, RowNmbr As Integer
Dim varOut() As Variant
Dim sText As String
Dim sPath As String
Dim sline As String

sPath = "C:\data.txt"

'Open file for read
Open sPath For Input As #1

Do Until EOF(1)
RowNmbr = RowNmbr + 1
Line Input #1, sText
If (Len(sText) = 0) Then
GoTo NFR_ENDLOOP
End If
If StrChk(sText) = True Then
ReDim Preserve varOut(i)
varOut(i) = sText 'RowNmbr
i = i + 1
End If
NFR_ENDLOOP:
Loop
Close #1

sline = varOut(0) ' Read the first line: "a123 Go now 123"

sline = Application.Substitute(sline, " ", "*", 1)
varTmp = Split(sline, "*")

MsgBox Trim(varTmp(1)) ' Fails to output "Go now 123"

End Sub


This works as expected...

Sub ParseTextFile2()
Dim vTmp
Const sLine$ = "a123 Go now 123"
vTmp = Split(Application.Substitute(sLine, " ", "*", 1), "*")
Debug.Print Trim(vTmp(1))
End Sub

...and so does this...

Sub ParseTextFile2()
Dim vTmp, sLine$
Const sFile$ = "C:\Data.txt"
sLine = ReadTextFile(sFile)
vTmp = Split(Application.Substitute(sLine, " ", "*", 1), "*")
Debug.Print Trim(vTmp(1))
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old May 26th 19, 10:54 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,084
Default Error during file read?

No.., it returns " Go now 123" BECAUSE sline has 4 spaces between "a123"
and "Go now 122" and your formula only replaces 1 of those spaces, leaving
the other 3 part of the 2nd array element.


Sorry.., didn't catch the Trim() line. It does indeed return the expected
result!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #4   Report Post  
Old June 3rd 19, 10:14 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 64
Default Error during file read?


This works as expected...

Sub ParseTextFile2()
Dim vTmp
Const sLine$ = "a123 Go now 123"
vTmp = Split(Application.Substitute(sLine, " ", "*", 1), "*")
Debug.Print Trim(vTmp(1))
End Sub


Yes! That one worked, but...


..and so does this...

Sub ParseTextFile2()
Dim vTmp, sLine$
Const sFile$ = "C:\Data.txt"
sLine = ReadTextFile(sFile)
vTmp = Split(Application.Substitute(sLine, " ", "*", 1), "*")
Debug.Print Trim(vTmp(1))
End Sub


This one still doesn't work. I've done experiments with
various file reading functions, including ReadTextFile(),
but they just aren't working. Also, I prefer not to
dump the array onto a new sheet.

I'm going to try to approach this another way. I'll be back soon.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable To Read File - Sumproduct error Fluke[_2_] Excel Worksheet Functions 9 May 26th 09 09:04 PM
Tried changing a file attribute from read only but I get error msg willymoore809 Excel Discussion (Misc queries) 5 June 5th 06 01:25 PM
"Unable to read file" error message when opening a Excel file that contains a PivotTable report. Tim Marsden Charts and Charting in Excel 2 October 15th 05 02:10 PM
Excel File - Read Only Error ritu Excel Discussion (Misc queries) 3 July 20th 05 06:59 AM
Read txt file for input error handler Chirs[_2_] Excel Programming 2 June 30th 05 12:40 PM


All times are GMT +1. The time now is 05:04 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017