PDA

View Full Version : Odd error -



theta
11-07-2012, 07:34 AM
I am not sure of the methods used here (Input, #fileID etc) but have adapted the code to work for my requirements.

On certain files I get the following error

Run-time error '62':
Input past end of file

Here is the code (with debug line in red) :


For aryFileLoop = LBound(aryFile) To UBound(aryFile)
Set cll = FILES.Cells(AnchorRow + 1 + aryFileLoop, fileName)
cll.Value = aryFile(aryFileLoop)

fileToOpen = cll.Value
flen = FileLen(fileToOpen)
If flen > 0 Then
fileid = FreeFile
Open fileToOpen For Input Access Read As #fileid Len = 10
myvar = Input(Application.Min(flen - 1, 10), #fileid)
Close #fileid
Else
myvar = "Zero length file"
End If
With cll.Offset(, 1)
.NumberFormat = "@"
.Value = myvar
End With

cll.Offset(, 2).Value = Mid(fileToOpen, InStrRev(fileToOpen, ".") + 1)

Next aryFileLoop


From the immediate window I have queried each argument and got the following results.

myvar = String of length 10
fileToOpen = and .xls file
flen = 4460544
fileID = 1

patel
11-07-2012, 07:51 AM
maybe Len = 10 does not mach with Application.Min(flen - 1, 10)

try eliminating Len = 10

theta
11-07-2012, 09:21 AM
The two values do match. And even when removing as suggested, I still get the same error :/

Stuck on this one, doesn't make any sense why this error would occur...

theta
11-07-2012, 09:37 AM
Could it be something to do with the fact that the file producing the error (fileToOpen) is an Excel (.xls) file?

If so, how would I get around this?

I have a list of 1,000 files and I need to read the first 10 chars of the file (as if viewed in a hex editor). Works except for this error.

theta
11-07-2012, 10:17 AM
maybe Len = 10 does not mach with Application.Min(flen - 1, 10)

try eliminating Len = 10


The problem appears to be trying to open an .xls .doc or office file as text. Excel tries to open it in it's own manner.

How can I force VBA to read a file as text, regardless of the extension?

patel
11-07-2012, 11:09 AM
If flen > 0 Then
fileid = FreeFile
Open fileToOpen For Input Access Read As #fileid
myvar = Input(10, #fileid)
Close #fileid
Else

theta
11-08-2012, 03:15 AM
Same error. It is because I am trying to open an .XLS as text I think. So i need to resolve that issue?

patel
11-08-2012, 04:42 AM
does it work with text file ?

theta
11-08-2012, 05:03 AM
does it work with text file ?

Yes it works with all files except office files (.xls, .doc etc)

It tries to open them in a different way - which is not compatible with what I am trying to acheive.

Any thoughts?

Aflatoon
11-08-2012, 06:18 AM
Perhaps you need to open them for binary access.

theta
11-08-2012, 07:35 AM
I tried that...the Input method is the one causing the problem I believe?

Even when trying to access as binary it still gives me the same error :/

shrivallabha
11-08-2012, 09:13 AM
I tried that...the Input method is the one causing the problem I believe?

Even when trying to access as binary it still gives me the same error :/

Aflatoon's suggestion seems to work:
Sub OpenExcelFileAsText()
Dim strTen
Open "C:\test.xls" For Binary Access Read Lock Read As #1
Line Input #1, strTen
Debug.Print Left$(strTen, 10)
Close #1
End Sub

However, I am not sure you'll be happy with the output you get with above code.

theta
11-08-2012, 09:41 AM
Aflatoon's suggestion seems to work:
Sub OpenExcelFileAsText()
Dim strTen
Open "C:\test.xls" For Binary Access Read Lock Read As #1
Line Input #1, strTen
Debug.Print Left$(strTen, 10)
Close #1
End Sub

However, I am not sure you'll be happy with the output you get with above code.

That worked BUT it took a great deal longer to run (almost a minute) whereas the previous code (commented out in green) shot through in seconds...what is causing the time to increase so dramatically? Is there any way to speed this up?



For aryFileLoop = LBound(aryFile) To UBound(aryFile)

Set cll = FILES.Cells(AnchorRow + 1 + aryFileLoop, fileName)
cll.Value = aryFile(aryFileLoop)

fileToOpen = cll.Value
flen = FileLen(fileToOpen)
If flen > 0 Then
fileID = FreeFile
'Open filetoopen For Input Access Read As #fileid Len = 10
Open fileToOpen For Binary Access Read Lock Read As #fileID
'myvar = Input$(Application.Min(flen - 1, 10), #fileID)
Line Input #fileID, strTen
myvar = Left$(strTen, 10)
Close #fileID
Else
myvar = "Zero length file"
End If
With cll.Offset(, 1)
.NumberFormat = "@"
.Value = myvar
End With

cll.Offset(, 2).Value = Mid(fileToOpen, InStrRev(fileToOpen, ".") + 1)

Set myvar = Nothing
Set cll = Nothing

Next aryFileLoop

Aflatoon
11-09-2012, 04:03 AM
Try leaving the len = 10
Open filetoopen For Binary Access Read As #fileid Len = 10

theta
11-09-2012, 04:32 AM
Try leaving the len = 10
Open filetoopen For Binary Access Read As #fileid Len = 10


Thanks :)

The results look different but consistent, so I can build a revised lookup table. Is there a function in excel to convert this binary into the value it would be (as text) if read using Input Access method?

One last thing I would need to do...check if the file is available? If I get an 'access denied' error it bombs out the code. How would one check the permissions of the file in the code provided?

Many thanks again...almost finished

snb
11-09-2012, 05:01 AM
The problem appears to be trying to open an .xls .doc or office file as text. Excel tries to open it in it's own manner

You don't want to read office files this way.
These methods are primarily aimed at text files (txt, html, csv, etc)
The tenth line in an office file has nothing to do with it's content. (the tenth line of an Excelfile doesn't represent the tenth row, nor the tenth paragraph in a Word file).
Please tell us what you want to use this for.

Aflatoon
11-09-2012, 05:02 AM
I have no idea what you mean - you are reading it into a text variable so what's the issue?

Add an error handler. ;)

theta
11-09-2012, 05:37 AM
I have no idea what you mean - you are reading it into a text variable so what's the issue?

Add an error handler. ;)

I thought there would a property to check if access is available?

If the file is open, it produces the error I believe...

Aflatoon
11-09-2012, 07:24 AM
So add an error handler to your code. ;) (there are no file objects in your code so there is no property you could refer to)

shrivallabha
11-09-2012, 08:45 AM
Maybe you can think of adding:
On Error Resume Next
'Before executing error prone line
On Error Goto 0
If Err.Number <> 0 then
Msgbox "Error processing file..."
End if