Import multiple csv files into separate columns in a worksheet

[anastasia1428]

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?

 

[Zot]

What is in column A? Already there to start with?

 

[anastasia1428]

What is in column A? Already there to start with?

Thanks Zot. All csv files have Column A and Column B. And Column A is the same in all csv files. I want to copy at least one Column A from one of the csv files say file 1.csv and all column b from all the csv files will be lined up next to each other in one worksheet. file names can be used as headers of the column. thank you

 

[Marc L]

​

Should be easier to help with at least some attachments and a complete explanation so without anything to guess …​

 

[anastasia1428]

​

Should be easier to help with at least some attachments and a complete explanation so without anything to guess …​

I don't know how to attach excel files but here's a snapshot of what my csv file looks like. I just want to compile all Column B next to each other into a single worksheet. I do not need to copy Coulmn A as they will be the same in all files. I want to have a final output as with the attached file Thanks all!

 

[Zot]

Install this macro in normal module in a fresh workbook. Once macro is run, it will ask to select a folder where you put all the files you wanted to copy from. Make sure you click on the folder until you see the Folder Name appears in the box and click OK.
Note: Make sure the folder you select only contains only the source file you wanted to copy. The program will loop all the files in there.

Option Explicit

Sub GetLastRow()

Dim SelectFolder As Integer
Dim x As Long, xx 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
xx = 2
'Loop through each file in a folder
For Each sFileName In FSOFolder.Files
    Set wb = Workbooks.Open(sFileName)
    wb.Sheets("Sheet1").Range("A1", wb.Sheets("Sheet1").Cells(Rows.Count, xx).End(xlUp)).Copy wsSummary.Cells(1, x)
    If x = 1 Then
        x = x + 1
        xx = xx - 1
    End If
    x = x + 1
    wb.Close True
Next

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

End Sub

 

[anastasia1428]

Install this macro in normal module in a fresh workbook. Once macro is run, it will ask to select a folder where you put all the files you wanted to copy from. Make sure you click on the folder until you see the Folder Name appears in the box and click OK.
Note: Make sure the folder you select only contains only the source file you wanted to copy. The program will loop all the files in there.

Option Explicit

Sub GetLastRow()

Dim SelectFolder As Integer
Dim x As Long, xx 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
xx = 2
'Loop through each file in a folder
For Each sFileName In FSOFolder.Files
    Set wb = Workbooks.Open(sFileName)
    wb.Sheets("Sheet1").Range("A1", wb.Sheets("Sheet1").Cells(Rows.Count, xx).End(xlUp)).Copy wsSummary.Cells(1, x)
    If x = 1 Then
        x = x + 1
        xx = xx - 1
    End If
    x = x + 1
    wb.Close True
Next

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

End Sub

Thanks, Zot. I tried to run the macro but gave me this error Sorry I am a newbie to coding.

 

[Zot]

I think you need to go VB Editor. Go to
Tools > References and then tick Microsoft Scripting Runtime.

 

[Zot]

Here is another reference if still a problem. Too many rules nowadays

Is there a code to turn on Microsoft Scripting Runtime Library?

I have used FileSystem Object in my code and it relies on the user having turned on his Microsoft Scripting Runtime Library. Is there a code by which I can turn on this library on the user's Excel

stackoverflow.com

 

[anastasia1428]

Install this macro in normal module in a fresh workbook. Once macro is run, it will ask to select a folder where you put all the files you wanted to copy from. Make sure you click on the folder until you see the Folder Name appears in the box and click OK.
Note: Make sure the folder you select only contains only the source file you wanted to copy. The program will loop all the files in there.

Option Explicit

Sub GetLastRow()

Dim SelectFolder As Integer
Dim x As Long, xx 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
xx = 2
'Loop through each file in a folder
For Each sFileName In FSOFolder.Files
    Set wb = Workbooks.Open(sFileName)
    wb.Sheets("Sheet1").Range("A1", wb.Sheets("Sheet1").Cells(Rows.Count, xx).End(xlUp)).Copy wsSummary.Cells(1, x)
    If x = 1 Then
        x = x + 1
        xx = xx - 1
    End If
    x = x + 1
    wb.Close True
Next

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

End Sub

Hi Zot

I was able to fix the error but when I tired to run the macro it copied Column A of each csv files instead.. See Columns C-G below..... It was supposed to copy the Column B of each files