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?

 

[Hasting013]

oh, yes, you're right. I didn't isolate the line properly. It's actually freaking out about the next line x = x + 1

 

[Joe4]

If you hover your mouse over the "x" when you get the error, what does it show as the value for "x" at that time?

 

[Hasting013]

If you hover your mouse over the "x" when you get the error, what does it show as the value for "x" at that time?

When I get the error, nothing shows up when I hover over X or anything else in the script in the background. If I select "OK" on the error dialogue, also nothing shows up upon hover-over as the cursor is now showing a typing symbol.

 

[Joe4]

Are you trying to use this code in the EXACT same manner as the original poster did?
If your situation or files are the least bit different, you might not be able to use that code without making changes to it.

 

[Hasting013]

Yes. I have a folder with csv files that all have two columns A and B, with each column being the same length.

 

[Joe4]

OK, you are sure that you only have CSV files of this format in that folder, and nothing else, right?

If so, try running this version of the code with error handling, and see what the MsgBox that comes back says:

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

On Error GoTo err_chk

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

Exit Sub


'Error handling routime below
err_chk:
    If Err.Number = 9 Then
        MsgBox "The value of x at the time of error is: " & x, vbOKOnly, "Run Time Error 9!!!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Application.ScreenUpdating = True

End Sub

 

[Hasting013]

This is what I get

 

[Joe4]

It sounds to me like it isn't working at all.
Are you sure the first file is being populated?
And are you sure that you have only CSV files in your folder and nothing else (and all CSV files have the same format)?
What file extensions do your files have?

 

[Hasting013]

Right, so the problem is the line above x = x + 1, which is

wb.Sheets("Sheet1").Range("A1", wb.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)).Copy wsSummary.Cells(1, x)

i was misunderstanding where the breakpoint feature worked.

Could I simply not have the right libraries activated?

As for the issue of file formatting, to isolate the issue of file formatting I created a test folder which I just populated with two "Microsoft Excel Comma Separated Variable" that just have a couple test numbers each. Still no dice and still the same error.

 

[Joe4]

Could I simply not have the right libraries activated?

If you have them selected, it should work.
An easy way to check is to try to compile the code before running it. If it compiles without issue, the code is file, and the issue is probably with your files/data.
To compile your code, go to the VB Editor and from the Debug menu, select "Compile VBA Project". Does that return any errors?

As for the issue of file formatting, to isolate the issue of file formatting I created a test folder which I just populated with two "Microsoft Excel Comma Separated Variable" that just have a couple test numbers each. Still no dice and still the same error.

Without having access to the files you are testing, I really cannot say. If you want to upload a few of these files to a file sharing site and share a link to them here, we can try to download them and run them against the code and see what happens. Just be sure to remove any sensitive data first.