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?
Split(dataFolder, ";")?