Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Import CSV files using Macro
i have 41 csv files that i need to import into 41 sheets in Excel. the name
of the csv files and the sheet names are the same minus the extension (.csv). The sheets will be existing sheets with headers. Please Help the Novice thanks M3ntz |
#2
|
|||
|
|||
Well, one way would be to use the Data-Import External Data-New Database
Query and select CSV files as the source database. Drawback: it's completey manual & you'll have to go through it 41 times Another way is to choose File-Open, select all 41 CSV files, and let Excel open them. Each will open in its own workbook. You can then copy and paste from each CSV workbook to the appropriate place in your existing sheets. Drawback: it's still lots of manual work, but should go quicker than the first option. Third way - write VBA code to open each CSV file in turn and copy the data into the correct existing sheet. "Mintz87" wrote: i have 41 csv files that i need to import into 41 sheets in Excel. the name of the csv files and the sheet names are the same minus the extension (.csv). The sheets will be existing sheets with headers. Please Help the Novice thanks M3ntz |
#3
|
|||
|
|||
Do you have the code for the VBA way of doing this? I have to keep this as
automated as possible. thanks "Duke Carey" wrote: Well, one way would be to use the Data-Import External Data-New Database Query and select CSV files as the source database. Drawback: it's completey manual & you'll have to go through it 41 times Another way is to choose File-Open, select all 41 CSV files, and let Excel open them. Each will open in its own workbook. You can then copy and paste from each CSV workbook to the appropriate place in your existing sheets. Drawback: it's still lots of manual work, but should go quicker than the first option. Third way - write VBA code to open each CSV file in turn and copy the data into the correct existing sheet. "Mintz87" wrote: i have 41 csv files that i need to import into 41 sheets in Excel. the name of the csv files and the sheet names are the same minus the extension (.csv). The sheets will be existing sheets with headers. Please Help the Novice thanks M3ntz |
#4
|
|||
|
|||
This is mostly plagiarized from Excel's VBA help file
It assumes 1) that all your 41 named tabs are in the same workbook, 2) you paste this code into a module in that workbook & run the code when that workbook is active, 3) the CSV files are in c:\CSV files\ and 4) you are going to paste the data starting with cell A2 For help on where & how to paste VBA code: http://www.cpearson.com/excel/codemods.htm For information on installing the code see Getting Started with Macros and User Defined Functions http://www.mvps.org/dmcritchie/excel/getstarted.htm Here's the code --------------------------------------------------------------------------- Sub OpenCSV() Dim i As Integer ' change this next line to reflect the actual directory Const strDir = "c:\csv files\" Dim ThisWB As Workbook Dim wb As Workbook Dim ws As Worksheet Dim strWS As String Set ThisWB = ActiveWorkbook Set fs = Application.FileSearch With fs .LookIn = strDir .Filename = "*.csv" If .Execute(SortBy:=msoSortByFileName, _ SortOrder:=msoSortOrderAscending) 0 Then For i = 1 To .FoundFiles.Count Set wb = Workbooks.Open(.FoundFiles(i)) strWS = wb.Sheets(1).Name wb.Sheets(1).UsedRange.Copy (ThisWB.Worksheets(strWS).Range("A2")) wb.Close False Next i Else MsgBox "There were no files found." End If End With End Sub ----------------------------------------------------------------------------------------------------------- "Mintz87" wrote: Do you have the code for the VBA way of doing this? I have to keep this as automated as possible. thanks "Duke Carey" wrote: Well, one way would be to use the Data-Import External Data-New Database Query and select CSV files as the source database. Drawback: it's completey manual & you'll have to go through it 41 times Another way is to choose File-Open, select all 41 CSV files, and let Excel open them. Each will open in its own workbook. You can then copy and paste from each CSV workbook to the appropriate place in your existing sheets. Drawback: it's still lots of manual work, but should go quicker than the first option. Third way - write VBA code to open each CSV file in turn and copy the data into the correct existing sheet. "Mintz87" wrote: i have 41 csv files that i need to import into 41 sheets in Excel. the name of the csv files and the sheet names are the same minus the extension (.csv). The sheets will be existing sheets with headers. Please Help the Novice thanks M3ntz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import data with macro | Excel Discussion (Misc queries) | |||
import data using macro | Excel Discussion (Misc queries) | |||
Import data from files with different names to EXCEL D'base. | Excel Discussion (Misc queries) | |||
Macro Help- combining "CS" files | Excel Discussion (Misc queries) | |||
How do I record a macro which should work on multiple files ? | Excel Discussion (Misc queries) |