0

I am trying to Write an excel macro that will prompt the user for a data folder, and then import all of the csv files the folder into their own new tab, the files are semi colon delimited, and we only need the first 2 column of data. Name each tab the part of the file name that occurred between the first two _ in the file name. If there is already a sheet with the same name, then add a number to the end of the sheet name starting with number 1 and going up.

From each file that is imported. In the sheet titled “Master Data Sheet” extract the following in the files that are being imported file names. Start inserting the data at line 6 and go down for each file added. The first 2 characters go into Column C. The first word after the first _ goes into column A, the second word after the first _ goes into column B. The data after the 2nd _ is the date, but it is in DD-MM-YYYY format, put that in column D in MM-DD-YYYY format. Lastly put the name of the sheet with the files data in column E.

This what I came up with:

    Sub ImportCSVFiles()
    
    Dim dataFolder As Variant
    dataFolder = Application.GetOpenFilename("CSV Files (*.csv),*.csv", , "Select Data Folder", , True)
    
    If VarType(dataFolder) = vbBoolean Then Exit Sub
    
    Dim dataFile As String
    Dim dataSheetName As String
    Dim dataSheetNumber As Integer
    Dim dataRowIndex As Long
    
    dataSheetNumber = 1
    
    Dim csvData As Variant
    Dim rowData As Variant
    
    Dim rowCounter As Long
    
    For Each dataFile In Split(dataFolder, ";")
        csvData = getFileData(dataFile)
        If IsArray(csvData) Then
            dataSheetName = getSheetName(dataFile)
            If Not sheetExists(dataSheetName) Then
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = dataSheetName
            Else
                dataSheetNumber = incrementSheetNumber(dataSheetName)
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = dataSheetName & " (" & dataSheetNumber & ")"
            End If
            dataRowIndex = 1
            For rowCounter = LBound(csvData) To UBound(csvData)
                rowData = Split(csvData(rowCounter), ";")
                If UBound(rowData) >= 1 Then
                    Worksheets(dataSheetName).Cells(dataRowIndex, "A").Value = rowData(0)
                    Worksheets(dataSheetName).Cells(dataRowIndex, "B").Value = rowData(1)
                    dataRowIndex = dataRowIndex + 1
                End If
            Next rowCounter
            Worksheets("Master Data Sheet").Cells(6 + rowCounter, "C").Value = Left(dataSheetName, 2)
            Worksheets("Master Data Sheet").Cells(6 + rowCounter, "A").Value = Split(dataSheetName, "_")(1)
            Worksheets("Master Data Sheet").Cells(6 + rowCounter, "B").Value = Split(dataSheetName, "_")(2)
            Worksheets("Master Data Sheet").Cells(6 + rowCounter, "D").Value = Format(DateValue(Split(dataSheetName, "_")(3)), "MM-DD-YYYY")
            Worksheets("Master Data Sheet").Cells(6 + rowCounter, "E").Value = dataSheetName
        End If
    Next dataFile

End Sub

Function getFileData(filePath As String) As Variant

    On Error GoTo errorHandler
    
    Dim fileData As String
    Dim fileNumber As Integer
    fileNumber = FreeFile
    Open filePath For Input As #fileNumber
    fileData = Input$(LOF(fileNumber), fileNumber)
    Close #fileNumber
    
    getFileData = Split(fileData, vbNewLine)

    Exit Function
    
errorHandler:
    
    MsgBox "Error reading file: " & filePath, vbExclamation
    getFileData = Null

End Function

Function getSheetName(filePath As String) As String

    Dim fileName As String
    fileName = Split(filePath, "\")(UBound(Split(filePath, "\")))
    
    getSheetName = Split(fileName, "_")(0) & "_" & Split(fileName, "_")(1)
    
End Function

Function sheetExists(sheetName As String) As Boolean
    Dim sheet As Worksheet
    For Each sheet In Worksheets
        If sheet.Name = sheetName Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
    sheetExists = False
End Function

Function incrementSheetNumber(sheetName As String) As Integer
    Dim sheetNumber As Integer
    sheetNumber = 1
    Dim sheet As Worksheet
    For Each sheet In Worksheets
        If Left(sheet.Name, Len(sheetName) + 2) = sheetName & " (" & sheetNumber & ")" Then
            sheetNumber = sheetNumber + 1
        End If
    Next sheet
    incrementSheetNumber = sheetNumber
End Function

But I am getting a Compile Error. What am I doing wrong?

6
  • Try declaring dataFile as Variant (ie. Dim dataFile As Variant). Commented Mar 23, 2023 at 12:04
  • Do you want processing only selected csv files (in the dialog), or all csv files in a chosen folder? If only selected, what do you expect from Split(dataFolder, ";")? Commented Mar 23, 2023 at 12:14
  • All csv, but they are semicolon delimited. Commented Mar 24, 2023 at 11:04
  • @Domenic I added that, but then on I get an error on ByRef argument type mismatch on the line " csvData = getFileData(dataFile)" Commented Mar 25, 2023 at 20:41
  • Since the argument for getFileData() is passed by reference, you must pass the exact data type for the procedure. So you'll need to convert your Variant variable dataFile to a String before passing it to getFileData(), for example dataSheetName = getSheetName(CStr(dataFile)). Commented Mar 26, 2023 at 2:55

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.