Import all CSV file starting in column B and put CSV file name in Column A

[jjcarter]

I am currently working with a macro I found here

My excel file currently sits in a directory with all the CSV files I need to import. The CSV information will get imported in column B starting in row 4. These are just single line information, so each csv file takes up one row. I then need the name of the file (without the .csv) to go beside its information in column A.

So test.csv information would paste in B4 and the name test would go in A4.

New CSV files periodically are downloaded to the folder where the workbook sits, so I would have to run this macro each time a CSV is downloaded to the folder. For that reason I need the macro to either ignore past .csv imports or start from fresh every time.

I think this VBA is close:

Public Sub ImportAllCSV()


    Dim FName As Variant, r As Long
    Dim destCell As Range
    Dim csvFolder As String
    
    csvFolder = ThisWorkbook.Path
    If Right(csvFolder, 1) <> "\" Then csvFolder = csvFolder & "\"
    
    With ActiveSheet
    .Cells.ClearContents
    Set destCell = .Cells(4, "B") 'OR Set destCell = .Range("B2")
    End With
    
    FName = Dir(csvFolder & "*.csv")
    Do While FName <> ""
        r = ImportCsvFile(csvFolder & FName, destCell)
        destCell.Offset(0, -1).Resize(r, 1).Value = FName
        Set destCell = destCell.Offset(r, 0)
        FName = Dir
    Loop
    
End Sub




Private Function ImportCsvFile(FileName As String, Position As Range) As Long
    With Position.Parent.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=Position)
        .Name = Replace(FileName, ".csv", "")
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ";"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
        ImportCsvFile = .ResultRange.Rows.Count
        .Delete
    End With
End Function

When I run this though, I get 'Run Time Error '68' Device Unavailable'. When I hit debug it highlights:

FName = Dir(csvFolder & "*.csv")

Any help is most appreciated.

 

[jjcarter]

FName = Dir(csvFolder & "*.csv")

.

After researching here

I looks like I can't use Dir on a Mac? What should I replace this line with for a Mac Excel 2011?

 

[Jim Gordon]

See this:
Working with Files and Folders (Mac)