err no csv found

[instanceoftime]

I use the code below in a macro. I point to a folder and it merges all csv files within that folder using this code. seems to work perfectly other than I get the error dialog box "no files csv".
It then merges the files in the folder (even though it says there isn't any). I could simply disable the error dialog but would rather fix the problem. Any ideas?

Sub merge()
'
' merge Macro
' f
'
' Keyboard Shortcut: Ctrl+f
'

'Sub ImportCSVsWithReference()

'UpdatebyKutoolsforExcel20151214

    Dim xSht  As Worksheet

    Dim xWb As Workbook

    Dim xStrPath As String

    Dim xFileDialog As FileDialog

    Dim xFile As String

    On Error GoTo ErrHandler

    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)

    xFileDialog.AllowMultiSelect = False

    xFileDialog.Title = "Select a folder [Kutools for Excel]"

    If xFileDialog.Show = -1 Then

        xStrPath = xFileDialog.SelectedItems(1)

    End If

    If xStrPath = "" Then Exit Sub

    Set xSht = ThisWorkbook.ActiveSheet

    If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear

    Application.ScreenUpdating = False

    xFile = Dir(xStrPath & "\" & "*.csv")

    Do While xFile <> ""

        Set xWb = Workbooks.Open(xStrPath & "\" & xFile)

        Columns(1).Insert xlShiftToRight

        Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name

        ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)

        xWb.Close False

        xFile = Dir

    Loop

    Application.ScreenUpdating = True

    Exit Sub

ErrHandler:

    MsgBox "no files csv", , "Kutools for Excel"

End Sub

 

[Micron]

Step thru your code with F8 and watch the flow to see what it is doing. You can mouse over variables on lines that have been executed to see the values, or use the immediate window to inquire, or open the watch and/or locals vbe window. As much as I can play with that, I can't replicate the issue. However, I did condense your code as it's unnecessarily stretched out so maybe I inadvertently fixed something.

 

[DanteAmor]

Check this:

Sub merge()
' merge Macro
' f
' Keyboard Shortcut: Ctrl+f
'Sub ImportCSVsWithReference()
'UpdatebyKutoolsforExcel20151214

    Dim xSht  As Worksheet
    Dim xWb As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    
    'On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Set xSht = ThisWorkbook.ActiveSheet
    If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\" & "*.csv")
    If xFile = "" Then
      MsgBox "no files csv", , "Kutools for Excel"
    Else
      Do While xFile <> ""
          Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
          Columns(1).Insert xlShiftToRight
          Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
          ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
          xWb.Close False
          xFile = Dir
      Loop
    End If
    Application.ScreenUpdating = True
End Sub

 

[Trebor76]

One reason is that there is (are) *.csv files that have no data