Import multiple csv files into separate columns in a worksheet

anastasia1428

New Member
Joined
Apr 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I have 54 csv files and I want to import into a single worksheet. All files have the same values for column A.
So I want to make a summary that looks like this. Can you help me with the code please?
1619496529124.png
 

Attachments

  • 1619496416399.png
    1619496416399.png
    70.1 KB · Views: 20

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
I did not try but I think this should work
VBA Code:
Sub GetLastRow()

Dim SelectFolder As Integer
Dim x As Long
Dim strPath As String
Dim wsSummary As Worksheet
Dim wb As Workbook
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim sFileName As Object

Set wsSummary = Sheet1
SelectFolder = Application.FileDialog(msoFileDialogFolderPicker).Show

If Not SelectFolder = 0 Then
    strPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
Else
    End
End If

Application.ScreenUpdating = False
'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(strPath)

x = 1
'Loop through each file in a folder
For Each sFileName In FSOFolder.Files
    Set wb = Workbooks.Open(sFileName)
    If x = 1 Then
        wb.Sheets("Sheet1").Range("A1", wb.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)).Copy wsSummary.Cells(1, x)
        x = x + 1
    Else
        wb.Sheets("Sheet1").Range("B1", wb.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)).Copy wsSummary.Cells(1, x)
    End If
    x = x + 1
    wb.Close True
Next

Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Application.ScreenUpdating = True

End Sub
 
Upvote 0
I did not try but I think this should work
VBA Code:
Sub GetLastRow()

Dim SelectFolder As Integer
Dim x As Long
Dim strPath As String
Dim wsSummary As Worksheet
Dim wb As Workbook
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim sFileName As Object

Set wsSummary = Sheet1
SelectFolder = Application.FileDialog(msoFileDialogFolderPicker).Show

If Not SelectFolder = 0 Then
    strPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
Else
    End
End If

Application.ScreenUpdating = False
'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(strPath)

x = 1
'Loop through each file in a folder
For Each sFileName In FSOFolder.Files
    Set wb = Workbooks.Open(sFileName)
    If x = 1 Then
        wb.Sheets("Sheet1").Range("A1", wb.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)).Copy wsSummary.Cells(1, x)
        x = x + 1
    Else
        wb.Sheets("Sheet1").Range("B1", wb.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)).Copy wsSummary.Cells(1, x)
    End If
    x = x + 1
    wb.Close True
Next

Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Application.ScreenUpdating = True

End Sub
Yesss it worked!! Thank you so much :):):)
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top