import multiple csv files

lomri

New Member
Joined
Jan 28, 2018
Messages
2
hi,
I found a neat code to import multiple csv files into one worksheet.
The problem I encountered is that I want to skip the first 2 lines of each csv. I tried searching everywhere how to do it but couldn't find what I was looking for.



Code:
[COLOR=#5F6A72][FONT=&quot]Option Explicit[/FONT][/COLOR]

[COLOR=#5F6A72][FONT=&quot]Sub ImportCSVsWithReference()[/FONT][/COLOR]
[B]'Author:    Jerry Beaucaire
'Date:      10/16/2010
'Summary:   Import all CSV files from a folder into a single sheet
'           adding a field in column A listing the CSV filenames

[COLOR=#5F6A72]Dim wbCSV   As Workbook[/COLOR]
[COLOR=#5F6A72]Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.[/COLOR][B][COLOR=#CC0000]Sheets("MasterCSV")[/COLOR]
Dim fPath   As String:      fPath = [B][COLOR=#CC0000]"C:\2010\Import\"[/COLOR]    [B]'path to CSV files, include the final \
[COLOR=#5F6A72]Dim fCSV    As String[/COLOR]

[COLOR=#5F6A72]If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _[/COLOR]
[COLOR=#5F6A72]    = vbYes Then wsMstr.UsedRange.Clear[/COLOR]

[COLOR=#5F6A72]Application.ScreenUpdating = False  [/COLOR][B]'speed up macro

[COLOR=#5F6A72]fCSV = Dir(fPath & [/COLOR][B]"*.csv"[COLOR=#5F6A72])         [/COLOR][B]'start the CSV file listing

[COLOR=#5F6A72]    Do While Len(fCSV) > 0[/COLOR]
[B]      'open a CSV file
[COLOR=#5F6A72]        Set wbCSV = Workbooks.Open(fPath & fCSV)[/COLOR]
[B]      'insert col A and add CSV name
[COLOR=#5F6A72]        Columns(1).Insert xlShiftToRight[/COLOR]
[COLOR=#5F6A72]        Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name[/COLOR]
[B]      [COLOR=#38761D]'copy date into master sheet and close source file[/COLOR]
[COLOR=#5F6A72]        ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)[/COLOR]
[COLOR=#5F6A72]        wbCSV.Close False[/COLOR]
[B]      [COLOR=#38761D]'ready next CSV[/COLOR]
[COLOR=#5F6A72]        fCSV = Dir[/COLOR]
[COLOR=#5F6A72]    Loop[/COLOR]
[COLOR=#5F6A72] [/COLOR]
[COLOR=#5F6A72]Application.ScreenUpdating = True[/COLOR]
[COLOR=#5F6A72]End Sub[/COLOR][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B]
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
Try
Code:
ActiveSheet.UsedRange.Offset(2).Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,621
Messages
5,523,947
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top