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]

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
View attachment 37733

My bad. I forgot that you need column B. Let me modify a bit

 

[Zot]

I did not try but I think this should work

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

 

[anastasia1428]

I did not try but I think this should work

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

 

[Zot]

Yesss it worked!! Thank you so much

Glad to be able to help. Nothing to do in office now ?

 

[Hasting013]

Glad to be able to help. Nothing to do in office now ?

I know I'm replying to a really old thread, but I am trying to do the exact same thing as OP. However, when I run this code I get the following error message:

I'm hoping you're still around and can help out. Any input is appreciated!

 

[Joe4]

I know I'm replying to a really old thread, but I am trying to do the exact same thing as OP. However, when I run this code I get the following error message:
View attachment 117606

I'm hoping you're still around and can help out. Any input is appreciated!

Did you look at posts 8 and 9 in this thread, and ensure that you have selected the appropriate libraries in order to use this code?

 

[Hasting013]

Did you look at posts 8 and 9 in this thread, and ensure that you have selected the appropriate libraries in order to use this code?

Yes I enabled those libraries, and the code runs, but it will only "scan-in" one .CSV file from my csv folder before it throws up this error code.

 

[Joe4]

Which line of code is it returning the error on?
If you are not sure, try stepping into your code and using the F5 to run the code one line at a time, until the error occurs, and then see which line of code it is on.

 

[Hasting013]

Which line of code is it returning the error on?
If you are not sure, try stepping into your code and using the F5 to run the code one line at a time, until the error occurs, and then see which line of code it is on.

It's throwing up the error on this highlighted line

 

[Joe4]

That doesn't seem to make sense. If the first one worked, it should have incremented x to 2 at that point, so it should not be hitting that line anymore.

Are your sure the first one is working?

Also, are you sure that your sheet names match (i.e. "Sheet1")?

And, are if you are failing on the second record, are you sure that the file name listed for the second file is correct?