Read other csv file name when some of the csv files are absent

user_10508660 0
Viewed 59

I created a MACRO to read csv file. However, when one of the csv files is absent, the macro stops reading the rest of the other csv files even though their csv files are available. So what should i do to rectify it such that it can proceed to read the other csv files even though one or even some of them are absent?

enter image description here

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

Dim filename As String
Dim location As String
location = Me.ComboBox2 'csv folder name

Dim ib As Integer
ib = 2
Do While ActiveWorkbook.Sheets("Index_AREA").Cells(ib, 41) <> ""
      filename = location & "\" & ActiveWorkbook.Sheets("Index_AREA").Cells(ib, 41) 'getting csv file name in the csv folder

Call readdatavcap3(filename, ib) 'another private sub to read data


    ib = ib + 1
Loop



End Sub
csv vba excel
2 Answers

Determine the last row to check before you start the loop, and continue until you reach it, instead of stopping at the first empty cell.

Also, use Long instead of Integer with row variables. It's not completely necessary in this case, but it's a good habit to get into if you ever have more than 32,767 rows.

In addition, using a With block will also make the code shorter, less redundant, more readable, and easier to change in the future.

Private Sub get_file_namevcap() 'check csv file using readdata sub
  Dim lSearchColumn As Long
  Dim lLastRow As Long
  Dim lRow As Long
  Dim filename As String
  Dim location As String

  location = Me.ComboBox2 'csv folder name

  ' convert column letters to a number
  lSearchColumn = Columns("AO").Column

  With ActiveWorkbook.Sheets("Index_AREA")
    lLastRow = .Cells(.Rows.Count, lSearchColumn).End(xlUp).Row
    For lRow = 2 To lLastRow
      ' getting csv file name in the csv folder
      filename = location & "\" & .Cells(lRow, 41) 
      ' skip if it's blank
      If Len(Trim(filename)) > 0 Then
        ' another private sub to read data
        readdatavcap3(filename, lRow) 
      End If
    Next
  End With 

End Sub
user_84229530

Try replacing the loop with

ib = 2
Do While ib <> ActiveWorkbook.Sheets("Index_AREA").Range("AO" & Rows.Count).End(XlUp).Row +1
if ActiveWorkbook.Sheets("Index_AREA").Cells(ib, 41) <> "" then
  filename = location & "\" & ActiveWorkbook.Sheets("Index_AREA").Cells(ib, 41) 'getting csv file name in the csv folder

Call readdatavcap3(filename, ib) 'another private sub to read data

end if
ib = ib + 1
Loop
user_98080630
Related