(Solved) – How to prompt the program to continue reading other csv files in csv folder even when one of the csv file is not found?

  • by

Cells(i,41) represents the csv file locations that i suppose the csv folder has it which i have stored them in my spreadsheet. However, some of my csv files are not in my csv folder but still i want the programme to continue to read what i have. Unfortunately, the programme stop with an error that says file path not found at the Set o_file = fs2.OpenTextFile line. I tried to solve the issue using error resume next and on error goto 0 but this time it cause another error at the line Do While o_file.atendofstream <> True which says object variable or with object variable not set error and i tried to use error resume next again but it still wont solve it.

Sub main()

call get_file_namevcap

end sub

Private Sub get_file_namevcap() 'check csv file using readdata sub

Dim i As Integer
Dim filename As String
Dim location As String
location = Me.ComboBox2
i = 2
Do While ActiveWorkbook.Sheets("IndexPTN_STI").Cells(i, 41) <> ""
      filename = location & "" & ActiveWorkbook.Sheets("IndexPTN_STI").Cells(i, 41) 'getting csv file name eg:"Vramp_bke_2019.09.28_09.10.51.csv"
Call readdatavcap1(filename, i)

    i = i   1
end sub

Private Sub readdatavcap1(filename As String, i As Integer)
Application.ScreenUpdating = False

Dim sl As String
Dim first As Integer
Dim second As Integer
Dim j As Long
Dim fs2 As New Scripting.FileSystemObject
Dim o_file As Scripting.TextStream
j = 2        'variable not defined at fs2
On Error Resume Next
Set fs2 = CreateObject("Scripting.FileSystemObject") 'FileSystemObject also called as FSO, provides an easy object based model to access computer's file system.
                                                      'o_file contains filename(csv file link)
Set o_file = fs2.OpenTextFile(filename, 1, TristateFalse) '1=Open a file for reading only. You can't write to this file. TristateFalse means u get ascii file by default
                                                          '2=ForWriting, 8= Forappending
On Error GoTo 0

 On Error Resume Next                     'o_file contains filename(text file data)
 sl = o_file.readline 'Reads an entire line (up to, but not including, the newline character) from a TextStream file and returns the resulting string.
 On Error GoTo 0

 On Error Resume Next
 Do While Left(sl, 1) = "#"   'Left Function is used to extract N number of characters from a string from the left side.
 On Error GoTo 0

 On Error Resume Next
 sl = o_file.readline
 On Error GoTo 0

 On Error Resume Next
 Do While o_file.atendofstream <> True 'atendofstream = Read-only property that returns True if the file pointer is at the end of a TextStream file; False if it is not.
 On Error GoTo 0

    sl = o_file.readline

    first = InStr(32, sl, ",", 1) - 15 'INSTR function returns the position of the first occurrence of a substring in a string.
On Error GoTo 0

    second = InStr(first   2, sl, ",", 1) 'syntax of InStr( [start], string, substring, [compare] )
                                          'start sets string position for each search, string = string being search, substring= string expression searched ,
                                          'eg:InStr(1, "Tech on the Net", "t") Result: 9    'Shows that search is case-sensitive
                                          'compare= optional 1= textcompare
                                          'searching for commas in the file in this case

         If second = 0 Then
         second = Len(sl)   1 'len=length of file string
         End If

    'Write the normal current trace
        ActiveWorkbook.Sheets("currentPTN_STI").Cells(j, 2 * i - 3) = Mid(sl, 15, first - 14)

         ' The MID function returns the specified number of characters in a text string, starting from a specified position (
       'ie. starting from a specified character number).
       'Use this function to extract a sub-string from any part of a text string. Syntax: MID(text_string, start_number, char_numbers).

        ActiveWorkbook.Sheets("currentPTN_STI").Cells(j, 2 * i - 2) = Abs(Mid(sl, first   2, second - 2 - first))   0.000000000000001
    On Error GoTo 0
     End If

 j = j   1


End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *