Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

LinkBack Thread Tools Display Modes
Old 09-27-2017, 03:41 PM
Alex99 Alex99 is offline Windows 7 64bit Office 2007
Join Date: Sep 2017
Posts: 1
Alex99 is on a distinguished road
Default Merging multiple CSV files into a new EXCEL workbook

Help needed!!

I have a bunch of individual CSV files and I would need to merge the third column of each CSV file into a new workbook. For example, the third column of the first CSV file goes into the first column of the new EXCEL file, the third column of the second CSV file goes into the second column of the new EXCEL file and so on.

Reply With Quote
Old 09-30-2017, 01:54 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,090
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice

Hi and welcome
Have a look at this addin by Ron de Bruin

It's safe, and using the " paste data next to each other " option will do what you need
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Reply With Quote
Old 10-30-2017, 03:34 PM
FionaMcKenzie FionaMcKenzie is offline Windows 10 Office 2016
Join Date: Oct 2017
Location: Surrey, United Kingdom
Posts: 20
FionaMcKenzie is on a distinguished road

Hi there,

Instead of using QueryTable, I recommend using ADO to do this.

I hope this code stub helps you work it out?

Private Function ImportCSVData() As Boolean

On Error GoTo ErrorHandler

Dim oConnection As ADODB.Connection
Dim oRecordset As ADODB.Recordset
Dim strSQL As String, strProvider As String, strADOConnection As String
Dim strFullFileName As String
Dim objSheet As Excel.Worksheet
Dim objStartCell As Excel.Range

ImportCSVData = False

' Turn Excel screenupdating off for speed reasons
Excel.Application.ScreenUpdating = False

' File name to do - in a loop perhaps
strFullFileName = "C:\TestData.csv"

' Your sheet name reference to do
Set objSheet = ActiveSheet

' Your range start to do, i.e. go to end & bring in next data
Set objStartCell = objSheet.Cells(objSheet.Range("A2").CurrentRegion. Rows.Count + 1, 1)

If FSO.FileExists(strFullFileName) Then

' Do a simple SQL query based on your data
strSQL = "Select [Unique Code], [2015], [2016], [2017] FROM " & strFullFileName

Set oConnection = New ADODB.Connection

strADOConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\" & ";" & _
"Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;"""

oConnection.Open strADOConnection

Set oRecordset = New ADODB.Recordset
oRecordset.ActiveConnection = oConnection
oRecordset.Source = strSQL
objStartCell.CopyFromRecordset oRecordset


ImportCSVData = True
End If

On Error Resume Next

' Always make sure screenupdating is back on
Excel.Application.ScreenUpdating = True
' Clear all object references from memory
Set oConnection = Nothing
Set oRecordset = Nothing
Set objSheet = Nothing
Set objStartCell = Nothing

Exit Function
MsgBox "An unexpected error has occurred" & vbCrLf & Err.Number & " " & Err.Description, _
vbOKOnly + vbInformation, "Import CSV Data"

GoTo CleanUp
End Function

' This you would typically put into a module called FSO
' You need to reference the Microsoft Scripting Runtime library first
' Select, Tools, References & find the Library in the list and tick it
Public Function FileExists(strFileFullName As String) As Boolean

Dim oFSO As New FileSystemObject

FileExists = oFSO.FileExists(strFileFullName)

Set oFSO = Nothing
End Function
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging Multiple Files by Name, Date abs004 Excel 1 03-02-2017 04:21 PM
Merging multiple fields within a Word letter from Excel LindsayV Mail Merge 2 04-08-2016 07:17 AM
Word does not put the files in the outbox of Outlook (merging with excel fields) Pannemanski Mail Merge 1 04-08-2016 03:58 AM
Merging word files from excel macro hklein Excel Programming 0 08-05-2011 02:27 AM
Merging Multiple Word files into One Stattovic Word 0 01-06-2010 07:19 AM

All times are GMT -7. The time now is 09:00 PM.

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc. is not affiliated with Microsoft