VBA to import selected data from csv from multiple files to one sheet

[picklefactory]

Hello folks
I have a wb to summarise data from an ever expanding series of csv source files and I've managed to build a routine to copy/paste the required data from those into my summary workbook. The csv's are auto created by a test rig, and it creates a new file for each test. The routine works well, but on reflection there are thousands of these files to be imported, and my code only does one at a time. Is there a way I could select a multiple of source files and process those en mass?
My current code is enclosed and is probably very clunky, but I'm a complete amateur.
How I've configured it is to select the source file via a dialogue window, that opens that file and copy/pastes selected cell data into an intermediate sheet for temporary storage, it then checks for any duplicates of Test Number, and if that's OK, it then finds the next empty row and copy/pastes across into the main sheet.
Is it possible to do this for multiple source files?
Sorry if this is very long winded, but it's the best I can manage I'm afraid.
Thanks folks

PS. I don't know if it's relevent, but it appears that the test rig is adding an .xls extension to the filenames, but when I open them, Excel is seeing them as .csv files. They do seem to be .csv files that the rig is putting the wrong extension on.

Sub Import_File()
'This code opens the file path to search for the required test data sheet to open via dialog box
'It checks for existing duplicate and then transposes dtat from test sheet to ALL PARTS sheet

'Issue ********************************
'The test data sheets have .xls extension, but are actually only .csv files, this causes the 'Text Import Wizard'
'to action as an additional task.
'**************************************
    
ChDrive "P" ' Changes to drive to search, this must be done before file path can be found below
ChDir "P:\Perkins Tier IV Oil Pump\7 - Facilities & Tools & Gauges\Tier 4 Data Folder\Test" ' Select desired path INCLUDING inserted values
MsgBox "Select required test data sheet from the next window" & vbNewLine & "NOTE! Just click 'Finish' in the 'Text Import Wizard'", , "SELECT SHEET TO IMPORT"
Application.Dialogs(xlDialogOpen).Show ' Open dialog box

'Copy and paste data from sheet to this work book in next available empty row
    ActiveSheet.Range("P1").Value = ActiveWorkbook.FullName
    ActiveSheet.Range("A1:P8").Select
    Selection.Copy
    Application.DisplayAlerts = False
    ActiveWindow.Close
    ActiveWorkbook.Sheets("Calcs").Select
    ActiveSheet.Range("A1").Select
    Application.DisplayAlerts = True
    ActiveSheet.Paste
    
    'Code to search 'Compiled' sheet for current test number and if found already there
    'then cease import with message box
    
    If WorksheetFunction.CountIf(Sheets("Compiled").Range("B:B"), Sheets("Calcs").Range("A12").Value) > 0 Then
    ActiveWorkbook.Sheets("Compiled").Select
    ActiveSheet.Range("B4").Select
    MsgBox "RESULTS FOR THIS TEST NUMBER ALREADY EXIST IN THE LIST", vbExclamation, "SELECT A DIFFERENT SHEET"
    GoTo NoImport
    End If


    'Code to find next empty row and fill with data from Calcs sheet
    ActiveWorkbook.Sheets("Compiled").Activate 'Select starting cell in record sheet, first cell in Test Number range
    Range("B4").Select
    
    Do
    If IsEmpty(ActiveCell) = False Then ' Search for next empty row
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    
    'Load data from user form to selected empty row
    ActiveCell.Value = ActiveWorkbook.Sheets("Calcs").Range("A12").Value
    ActiveCell.Offset(0, 1) = ActiveWorkbook.Sheets("Calcs").Range("F1").Value
    ActiveCell.Offset(0, 2) = ActiveWorkbook.Sheets("Calcs").Range("B1").Value
    ActiveCell.Offset(0, 3) = ActiveWorkbook.Sheets("Calcs").Range("D5").Value
    ActiveCell.Offset(0, 4) = ActiveWorkbook.Sheets("Calcs").Range("E5").Value
    ActiveCell.Offset(0, 5) = ActiveWorkbook.Sheets("Calcs").Range("D6").Value
    ActiveCell.Offset(0, 6) = ActiveWorkbook.Sheets("Calcs").Range("D7").Value
    ActiveCell.Offset(0, 7) = ActiveWorkbook.Sheets("Calcs").Range("E7").Value
    ActiveCell.Offset(0, 8) = ActiveWorkbook.Sheets("Calcs").Range("D8").Value
    ActiveCell.Offset(0, 9) = ActiveWorkbook.Sheets("Calcs").Range("E8").Value
    

NoImport:

End Sub

 

[VoG]

Hi.

Maybe these will help http://www.rondebruin.nl/txtcsv.htm http://www.rondebruin.nl/csv.htm

 

[picklefactory]

Thanks VoG, that might move me forward. I've got that working even though the csv files are 'disguised' as xls files and that has imported cleanly the group I selected into individual sheets in my wb. I don't want to keep all of those though, so i just need to work out how to summarise the selected cells from each new sheet into my summary sheet and then delete all those new tabs again.


I'm taking a deep breath and going for it.

Thanks again, I'll probably be back though (