VBA Error 62 Input past end of file due to characters

Hi all,

We receive a file from a third party and need to read it in. I'm getting an error 62 due to corruption/characters in their file. I've attached a very small file showing the current character causing problems. Is there any way I can get past this? At the moment I'm manually opening the file and deleting the character(s), but that's not acceptable.

Private Sub doImportFile()
ImportFile "H:\dat.txt", ""
End Sub

Private Function ImportFile(sLoc As String, ByRef Contents As String) As Boolean
  On Error GoTo onExcept

  Dim vff                                         As Long
  Dim lngChars                                    As Long
  Dim sLine                                       As String
  Dim sStr                                        As String

  vff = FreeFile
  Open sLoc For Input As #vff
  lngChars = LOF(vff)
  Do Until EOF(vff) 'this only gets up to illegal character in file
    Line Input #vff, sLine
    sStr = sStr & sLine
  Loop
Debug.Print sStr 

sStr= Input(lngChars, vff) 'this throws an error 62
Debug.Print sStr

Finally:
  Close vff
  Exit Function

onExcept:
Debug.Print Err.Number & ": " & Err.Description
  GoTo Finally

End Function

Open in new window


I've tried the two methods I know as per embedded code. First only gets to '12345', rather than '123456789ABCDEFGHIJ'.  I need to read in the whole file, of course.  sStr= Input(lngChars, vff), it gives an Error 62.

The preferred solution I guess is to be able to parse the contents without getting caught out by the character(s) prematurely signalling EOF, although also I guess can pre-process file to remove? Any advice welcome!

Many thanks

K.
dat.txt
katerina-pAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If it helps, the invalid character is ASCII 26.
0
Martin LissOlder than dirtCommented:
Try this.

Private Function ImportFile(sLoc As String, ByRef Contents As String) As Boolean
  On Error GoTo onExcept

  Dim vff                                         As Long
  Dim lngChars                                    As Long
  Dim sLine                                       As String
  Dim sStr                                        As String
  Dim strParts() As String
  Dim lngLine As Long
  
  vff = FreeFile
  'Open sLoc For Input As #vff
  Open sLoc For Binary As #vff
  sLine = Input$(LOF(vff), vff)
  sLine = Replace(sLine, Chr(26), "")
  
  strParts = Split(sLine, vbCrLf)
  
'  lngChars = LOF(vff)
'  Do Until EOF(vff) 'this only gets up to illegal character in file
'    Line Input #vff, sLine
'    sStr = sStr & sLine
'  Loop
    For lngLine = 0 To UBound(strParts)
        sStr = sStr & sLine
    Next
Debug.Print sStr

'sStr = Input(lngChars, vff) 'this throws an error 62
'Debug.Print sStr

Finally:
  Close vff
  Exit Function

onExcept:
Debug.Print Err.Number & ": " & Err.Description
  GoTo Finally

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
katerina-pAuthor Commented:
Many thanks

Within the For..Loop I guess there is a typo and it should read sStr = sStr & strParts(lngLine)
0
Martin LissOlder than dirtCommented:
Yes <embarrased>


You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
katerina-pAuthor Commented:
You're embarrassed?! I thought I'd checked Input$ and it hadn't worked! :) And I was aware of Open for Binary yet hadn't realised it would help! Anyway, thank you for the help.

Will check out the links in your profile!

Many thanks :)

Kx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.