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

Question

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?

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

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. https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row — braX, May 20 '20 at 05:38
Change you loop like `Do While ib <> ActiveWorkbook.Sheets("Index_AREA").Range("AO" & Rows.Count).End(XlUp).Row +1` — Naresh, May 20 '20 at 05:46
@NareshBhople do i replace do while ib <> Range with ib = 2 in this case while the rest the same? — john, May 20 '20 at 05:48
No that won't be valid. It will throw an error... Let the ib = 2 and then change the loop as mentioned above. Also, there is an answer added, please check if it works. — Naresh, May 20 '20 at 05:50
@Naresh Bhople but i still need the Do While ActiveWorkbook.Sheets("Index_AREA").Cells(ib, 41) <> "" to check for empty csv link, so i suppose i cant replace that? — john, May 20 '20 at 06:11

braX · Answer 1 · 2020-05-20T06:27:00.367

2

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

edited May 20 '20 at 06:27

answered May 20 '20 at 05:44

braX

11,506
5
20
33

1

You probably want to include a check for empty filenames =) – JvdV May 20 '20 at 06:07
@braX hey thanks for the reply but subsequently it doesn't work for other part of the macro for some reasons which is not your fault – john May 20 '20 at 06:32
Well, then that would be for another question. :) – braX May 20 '20 at 06:33
Like subsequently when i call the readdatavcap3(filename,lRow), i got the bad file name or ref – john May 20 '20 at 06:33
I have no idea what that function is, as you wrote it, but you can use `Debug.Print filename` right before the call to the other function to see what it is. Put a `Stop` right after that line and check it. – braX May 20 '20 at 06:34
Sounds good. Please remember to accept the answer using the check mark at the top left of the answer if it is complete. – braX May 20 '20 at 06:35

Naresh · Accepted Answer · 2020-05-20T06:26:54.890

1

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

edited May 20 '20 at 06:26

answered May 20 '20 at 06:20

Naresh

2,984
2
9
15

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

2 Answers2