Create Excel Macro to extract data from several files

We received several hundred Excel files from a client which represent individual invoices. In order to be able to do statistics on these invoices we would like to extract the relevant information (client number, date, client name, amounts) and list them in a separate aggregated file. Data Description ------------------------------------- The client used 3 different (but very similar) template types for his invoices (which he typed by hand!): - Professional - Clubs - Private customers (VAT included in amounts) The type of data and location of the data changes somewhat depending for the Private invoices, since this one includes VAT in the amounts. Every invoice has exactly one page (if it was 2 pages, he made 2 invoices). The fields of interest are listed in the Excel File "Fields.xls" and also include file name and creation date. The data is always in the worksheet titled "Rechnung (2)". The Excel files include Macros which you can ignore. Deliverables ------------------------------------- We need 2 macros that work on any one of the 3 types of invoice (so there is either a separate macro for Private files or a possibility to specify the type of files treated). We should be able to specify a directory containing any amount of files of the same type (no mix between Professional and Private) and receive the following output: Macro1 : output is a flat file containing all the fields specified in the file "Fields.xls" in one long table. Every file should generate one line in the output file. Macro2: output is a flat file containing all the fields specified in the file "Fields.xls" in one long table. Since this can contain several lines for each file, the header information (filename, date) should be repeated for each line. The macro should stop at the first empty line and not copy the empty lines into the output file. Every file can hence generate up to 20 lines. Files included ------------------------------------- [login to view URL] => list of fields to be copied from original files and addresses of the relevant fields Empty [login to view URL] => Empty example files Examples [login to view URL] => 4 example files for Private invoices Examples [login to view URL] => 4 examples of Professional files