Importing Multiple Files into one spreadsheet

Hey guys,

So I have to import 300 files or so to update a price sheet so we can graph historical prices for something. I was hoping you could help me get the correct VBA/macro to import a folder full of the files that currently contain the old prices, and import a specific column/data from each file and have it transfer to a new sheet. Please let me know if this is possible! I've attached a dumbed down format of what one of the files I'd be extracting the prices from.

Any help would be greatly appreciated. Don't really know what I'll do if I can't get this to work hah.

I tried something along the lines of this, but haven't found any success.

Option Explicit

Sub ImportCSVsWithReference()
'Author: Jerry Beaucaire
'Date: 11/3/2011
'Summary: Import all CSV files from a folder into a single sheet
' adding a field in row 1 for listing the CSV filenames

Dim wbCSV As Workbook
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("MasterCSV")
Dim fPath As String: fPath = "H:\Renewable" 'path to CSV files, include the final \
Dim fCSV As String
Dim NextCol As Long

If MsgBox("Clear the existing MasterCSV sheet before importing?", _
vbYesNo, "Clear?") = vbYes Then
wsMstr.UsedRange.Clear
NextCol = 1
Else
NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
End If

Application.ScreenUpdating = False 'speed up macro

fCSV = Dir(fPath & "*.csv") 'start the CSV file listing

Do While Len(fCSV) > 0
'open a CSV file
Set wbCSV = Workbooks.Open(fPath & fCSV)
'insert row 1 and add CSV name
Rows(1).Insert xlShiftDown
Range("A1") = ActiveSheet.Name
'copy date into master sheet and close source file
ActiveSheet.UsedRange.Copy wsMstr.Cells(1, NextCol)
wbCSV.Close False
'ready next CSV
fCSV = Dir
NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
Loop

Application.ScreenUpdating = True
End Sub


If there is an easier way to handle this, please let me know.

bump bump

another day, same problem

anything? anything that would make the process easier

still working on this

if anyone can help out or give me any direction on this, it would be appreciated.