Bulk Import of CSV Files with Excel VBA: An Efficient Data Integration Technique.

Sub ImportAllCSVFilesToNewWorkbook()
    Dim MyFolder As String
    Dim MyFile As String
    Dim newWb As Workbook
    Dim ws As Worksheet
    Dim sheetName As String
    Dim newFileName As String
    
    ' Prompt the user to enter a new file name
    newFileName = InputBox("Please enter the name for the new Excel file (no extension):")
    If newFileName = "" Then Exit Sub ' Exit if cancelled or nothing was entered
    
    On Error GoTo ErrorHandler ' Error handling
    
    ' Get the path of the folder of the current Excel file
    If ThisWorkbook.Path = "" Then
        MsgBox "This workbook has not been saved yet. Please save it first."
        Exit Sub
    End If
    
    MyFolder = ThisWorkbook.Path & "\"
    
    ' Create a new workbook
    Set newWb = Workbooks.Add
    
    ' Find the first CSV file in the specified folder
    MyFile = Dir(MyFolder & "*.csv")
    
    Do While Len(MyFile) > 0
        ' Create a worksheet name from the file name, excluding the extension
        sheetName = Left(MyFile, InStrRev(MyFile, ".") - 1)
        
        ' Remove invalid characters for sheet name (optional)
        sheetName = Replace(sheetName, "\", "")
        sheetName = Replace(sheetName, "/", "")
        sheetName = Replace(sheetName, "?", "")
        sheetName = Replace(sheetName, "*", "")
        sheetName = Replace(sheetName, "[", "")
        sheetName = Replace(sheetName, "]", "")
        
        ' Truncate if longer than 31 characters (optional)
        If Len(sheetName) > 31 Then
            sheetName = Left(sheetName, 31)
        End If
        
        ' Add a new worksheet and set its name
        Set ws = newWb.Worksheets.Add
        ws.Name = sheetName
        
        ' Import the CSV file into the new worksheet
        With ws.QueryTables.Add(Connection:="TEXT;" & MyFolder & MyFile, Destination:=ws.Range("A1"))
            .TextFileParseType = xlDelimited
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        
        ' Find the next CSV file
        MyFile = Dir()
    Loop
    
    ' Save the new workbook
    newWb.SaveAs Filename:=MyFolder & newFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    
    Exit Sub
    
ErrorHandler: ' Error handling
    MsgBox "An error occurred: " & Err.Description
End Sub

' MIT License
' Copyright (c) 2023 [ANJI]
'
' Permission is hereby granted, free of charge, to any person obtaining a copy
' of this software and associated documentation files (the "Software"), to deal
' in the Software without restriction, including without limitation the rights
' to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
' copies of the Software, and to permit persons to whom the Software is
' furnished to do so, subject to the following conditions:
'
' The above copyright notice and this permission notice shall be included in all
' copies or substantial portions of the Software.
'
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
' IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
' FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
' AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
' LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
' OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
' SOFTWARE.