1

working with an excel macro i found Here

i am trying to import ~6000 text files into excel. it works well for a few hundred files, and then i get an error 62 past end of file error.

Sub Test()
     Call ScanDir("\\es.msu.edu\ipf\BPS\HVACDOCS\ALMS\")
End Sub


Private Sub ScanDir(ByVal DirPath As String)
    Dim oCurFile As String
    oCurFile = Dir(DirPath)

Dim oCurRow As Long
oCurRow = 2
Dim oFile As String
Do While oCurFile <> ""
    Open DirPath & oCurFile For Input As #1
    oFile = Input(LOF(1), 1)
    Close #1

    Sheet1.Cells(oCurRow, 1).Value = oCurFile
    Sheet1.Cells(oCurRow, 2).Value = oFile

    oCurFile = Dir()
    oCurRow = oCurRow + 1
    oFile = ""
  Loop 
End Sub

Now, VBA is not my thing, but a google search indicates the following:

An Input # or Line Input # statement is reading from a file in which all data has been read 
or from an empty file. Use the EOF function immediately before the Input # statement to detect 
the end of file.

So I thought that changing oCurFile <> "" to Not EOF(1) but then i get a bad file name error, I'm honestly not sure why? i don't fully understand the purpose of oCurFile <>"" i thought it was essentially "do this while the file name isn't blank" can i add an "And Not EOF(1) somehow?

i mean, i can't literally do that, cause it gives me an error "Bad file name"

i'm not sure where i am going wrong?

seuadr
  • 141
  • 11
  • When you get the error, isn't it a 0 length file? – Black cat May 24 '23 at 11:03
  • For the error `Input past end of file`, see if [this](https://stackoverflow.com/questions/76068736/error62-input-past-end-of-file-on-vba-script-to-search-directory-for-files-cont) helps. – Domenic May 24 '23 at 12:10
  • If Not EOF(1) then oFile = Input(LOF(1), 1) ............. oFile = "" End if – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ May 24 '23 at 16:24
  • thanks for the input all - Blackcat all of the files have at least a few dozen characters and are 2+ kb so i assume that they aren't a zero length, unless it is as @Domenic's link discussed - input doesn't count the character, but, LOF does. – seuadr May 25 '23 at 11:39

0 Answers0