Read data from closed sheets into new spreadsheet.

[Lynton]

I have over 900 individual invoices created by excel, all are stored in a directory d:\invoices and are named Inv10001.xlsx to inv100909.xlsx
I have a new spreadsheet called Address Book with one sheet called Address list.

I want a macro or a vba code to open each of the invoice files and copy the addresses within the files to the address list sheet, then close the invoice file. this needs to happen to all the invoices.

I want to copy the field data from

Inv(files) Address list
A13 - A1
A14 - B1
A15 - C1
A16 - D1
A17 - E1
A18 - F1
A19 - G1
A20 - H1

Hopefully this should give me all the addresses from all of the invoices in new sheet.
any help appreciated.

 

[Yongle]

Are there any non-invoice files in that folder ? ( Is it safe to treat every file the same or does VBA need to check that name begins Inv...)
One of the 2 invoice numbers quoted in your post is not correct

 

[Lynton]

thanks for the reply'
invoices number from inv100001 to inv100909, only invoice files in that folder.