Using macros to open any .csv files from specifed directory

[ClarkE]

Greetings!

Is it possible to write a macro that will look into a specific directory and open any .csv files that exist in it? The filenames could change from one day to the next. The directory may even be empty! Can it loop until all files processed or do I have to keep running the macro? Having asked all thi, what is the macro?

I have been asked if it is possible to build a 'master' workbook that opens such files, copies the data (standardized) into an 'assembler' workbook, in which some formulae will assemble various scripts and scenarios; the macro then pastes everything as values to a flat workbook, and saves as a reference file. A reference file needs to be created for each .csv file existing in the specified directory. Whilst I can write a macro to open specific files (courtesy of your message board - thanks!), I now need to put some 'intelligence' into it.

Can you help me with this, please?

Kind regards,

Rob.

 

[Andrew Poulsom]

Use the Dir function like this:

Sub Test()
    Dim FoundFile As String
    Dim FileSpec as String
    FileSpec = "C:TEMP*.CSV"
    FoundFile = Dir(FileSpec)
    If FoundFile = "" Then Exit Sub
    '*** Your code to process first Foundfile goes here***
    Do While FoundFile <> ""
        FoundFile = Dir()
        If FoundFile <> "" Then        
            '*** Your code to process next FoundFile goes here***
        End If
    Loop
End Sub

Change the FileSpec to suit.