Read in Data

[Ronan]

He All, im looking to read in data from csv files contained in approx 110
files. the files are all set up the same way and i want to calculate a
summary of each file and then "upload" this summary into a single excel file.
I presume the only way of doing this is through VBA. I can use VBA but am not
competent so the easiest way is the best.

Thanks

 

[Joel]

Try this. It iwll open a dialog box to let you select a folder

Sub MakeSummary()


Dim lngCount As Long

' Open the file dialog
Set folderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With folderPicker
.InitialFileName = "c:\temp\"
.Show
If .SelectedItems.Count = 0 Then

MsgBox ("Cannot Get foler - Exiting Macro")
Exit Sub
End If

Folder = .SelectedItems.Item(1)
Folder = Folder & "\"

End With

FName = Dir(Folder & "*.csv")
Do While FName <> ""
Set NewSht = ThisWorkbook.Sheets.Add()
NewSht.Name = FName
LastRow = NewSht.Range("A" & Rows.Count).End(xlUp).Row
If LastRow = 1 And NewSht.Range("A1") = "" Then
NewRow = 1
Else
NewRow = LastRow + 1
End If

With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & Folder & FName, _
Destination:=NewSht.Range("A" & NewRow))
.Name = FName
.SaveData = True
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With

FName = Dir()
Loop

End Sub