Import CSV files using Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Do you have the code for the VBA way of doing this? I have to keep this as
automated as possible. thanks
 
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 Su
 
Back
Top