Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-27-2017, 03:41 PM
Alex99 Alex99 is offline Windows 7 64bit Office 2007
Novice
 
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.

Thanks!
Reply With Quote
  #2  
Old 09-30-2017, 01:54 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,940
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

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
Reply With Quote
  #3  
Old 10-30-2017, 03:34 PM
FionaMcKenzie FionaMcKenzie is offline Windows 10 Office 2016
Novice
 
Join Date: Oct 2017
Location: Surrey, United Kingdom
Posts: 20
FionaMcKenzie is on a distinguished road
Default

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
oRecordset.Open
objStartCell.CopyFromRecordset oRecordset

oRecordset.Close
oConnection.Close

ImportCSVData = True
End If

CleanUp:
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
ErrorHandler:
MsgBox "An unexpected error has occurred" & vbCrLf & Err.Number & " " & Err.Description, _
vbOKOnly + vbInformation, "Import CSV Data"

Err.Clear
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
Reply

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 2 01-15-2010 10:16 AM


All times are GMT -7. The time now is 10:37 AM.


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