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 Loop 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 Loop 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 Loop o_file.Close End Sub