VBA Code to COMBINE Multiple CSV Files Horizontally Into One Excel File

mrpayne

New Member
Joined
Dec 13, 2018
Messages
6
As the title notes I have multiple .csv files that I need to combine horizontally into one Excel File but only need some of the columns information for this project. Examples of two of the csv is below. Each csv has about 300 rows and the below 5 comma separated columns. Each csv represents a daily feed I receive with the same 1st column information as the consistent identifier. At times I may need to combine 28 days of csvs and some combination may be 35 days it just depends on situation.

1st Day csv example

57169, 8101, 781370 , 12/12/2018, 400
40000, 1201, 600000 , 12/12/2018, 300
........298 more rows

Next day csv example

57169, 8101, 781570 , 12/13/2018, 200
40000, 1201, 600200 , 12/13/2018, 200
........298 more rows

RESULTS DESIRED:
What would I like as an end result in an .xlsx file with the below format:
12/12/2018 12/13/2018 ........ 26 to 33 more days
57169 781370 781570 ........
40000 600000 600200 ........

I am not even a good VBA coder by any means just someone who dabbles. I have found some code items that seem to go this direction but I have not been able to piece it together. Frustration is beginning to set in. If this is not possible to put the dates above each column in the Excel file is it possible to at least get the columns in date order oldest to newest in column order without headers.

I would so very much appreciate any guidance and will be glad to share any code I have tried to date but wanted to put this out first to see if there are any suggestions or someone who can point me in the right direction. If you already have some code that you want to share again thank you very much.

Thanks in Advance,
M Payne
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sorry the dates on my Excel File example are supposed to be over the 2nd and 3rd column examples. Thanks.
 
Upvote 0
Here is some sample code I am having trouble with (found on the net)....Currently it goes to the location folder but it does not show or seem to find any files even if I have CSV files in the folder. It has me stumped. The system acts like it is trying to work and not providing me an error but it just produces a blank screen after showing busy for a short amount of time. Does anyone see the hang up on this?

Sub ImportandCombineCSV()
'Import Multiple CSV files to create side by side files
Dim xSht As Worksheet
Dim xWb As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xCount As Long
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = True
xFileDialog.Title = "Select A Folder"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
Set xSht = ThisWorkbook.ActiveSheet
If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Import CSV Files") = vbYes Then
xSht.UsedRange.Clear
xCount = 1
Else
xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1
End If
Application.ScreenUpdating = False
xFile = Dir(xStrPath & "" & "*.csv")
Do While xFile <> ""
Set xWb = Workbooks.Open(xStrPath & "" & xFile)
Rows(1).Insert xlShiftDown
Range("A1") = ActiveSheet.Name
ActiveSheet.UsedRange.Copy xSht.Cells(1, xCount)
xWb.Close False
xFile = Dir
xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1
Loop
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "no csv files found", , "Error on Import"
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,991
Messages
6,175,821
Members
452,672
Latest member
missbanana

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