Skip to content
The Chat Archive
gpt-4-0314· OpenAI WildChat-4.8M (AI2)

I need to create an Excel macro (VBA code) that all .csv files in a folder and writes…

  1. User

    I need to create an Excel macro (VBA code) that all .csv files in a folder and writes all content in the same excel worksheet

  2. Assistant

    This VBA code will open all .csv files in a folder and write their content to the same Excel worksheet. Please replace "C:\YourFolderPath" with the path to the folder where your .csv files are located.

    Option Explicit
    
    Sub ImportCSVFiles()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim myPath As String
        Dim myFile As String
        Dim csvFile As String
        Dim importRange As Range
        Dim lastRow As Long
    
        ' Set path to the folder with CSV files
        myPath = "C:\YourFolderPath\"
    
        ' Check if the path exists
        If myPath = "" Then
            MsgBox "No path specified! Please enter a valid folder path.", vbExclamation, "Error"
            Exit Sub
        End If
        
        ' Set the active Excel workbook and target worksheet
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets(1)
        ws.Cells.ClearContents
        
        ' Check if folder exists
        If Len(Dir(myPath, vbDirectory)) = 0 Then
            MsgBox "Folder does not exist. Please check the folder path.", vbExclamation, "Error"
            Exit Sub
        End If
    
        ' Loop through all CSV files in the folder
        csvFile = Dir(myPath & "*.csv")
        Do While Len(csvFile) > 0
            ' Open CSV file
            Workbooks.Open Filename:=myPath & csvFile, Format:=6
            
            ' Find first empty row
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            ' Copy data from CSV file
            Set importRange = ActiveSheet.UsedRange
    
            ' Paste data to Excel target worksheet
            If lastRow > 1 Then lastRow = lastRow + 1
            importRange.Copy Destination:=ws.Range("A" & lastRow)
            
            ' Close CSV file without saving
            Workbooks(csvFile).Close SaveChanges:=False
            
            ' Move to the next CSV file
            csvFile = Dir
        Loop
    
        ' Message box to display the successful completion of the import
        MsgBox "All CSV files have been imported successfully.", vbInformation, "Import Completed"
    End Sub
    

    To use the macro, open Excel, press ALT + F11 to enter the Visual Basic for Applications editor. From the menu, select Insert -> Module, then paste the code into the module window. Change the folder path to the folder containing your .csv files. Close the VBA editor, press ALT + F8 to open the "Macro" window in Excel, then run the "ImportCSVFiles" macro.

  3. User

    Thanks. Now please give me other VBA code to ask the macro user to interactively select a folder, and save its local path

  4. Assistant

    You can use the following VBA code to ask the user to interactively select a folder. The folder's local path will be stored in the selectedFolderPath variable.

    Option Explicit
    
    Sub SelectFolder()
        Dim folderDialog As FileDialog
        Dim selectedFolderPath As String
    
        ' Create a FileDialog object as a Folder Picker dialog box
        Set folderDialog = Application.FileDialog(msoFileDialogFolderPicker)
        
        ' Show the Folder Picker dialog box
        If folderDialog.Show = -1 Then
            ' Get the selected folder's path
            selectedFolderPath = folderDialog.SelectedItems(1)
            
            ' Display the folder path in a message box
            MsgBox "Selected folder path: " & selectedFolderPath, vbInformation, "Folder Path"
        Else
            MsgBox "No folder was selected.", vbExclamation, "No Selection"
        End If   
    End Sub
    

    To use the macro, follow the same steps as before: open Excel, press ALT + F11 to enter the Visual Basic for Applications editor, and insert a new module. Then paste the code into the module window. Close the VBA editor, press ALT + F8 to open the "Macro" window in Excel, and run the "SelectFolder" macro. The user will be prompted to select a folder, and a message box will display the selected folder's local path.