PDA

View Full Version : Import mutiple files from a folder to excel files



Dreamer
08-14-2008, 09:17 AM
Dear all,

Would be appreciated if anyone can help me to achieve this, thanks!!

i have a folder, e.g. C:\test, containing around 80 html files.

How can I do a loop to read the contain of each html file and export it to individual excel file..

e.g.

Read C:\test\a1.html --> paste all content to a new workbook--> save the excel file name as C:\test\a1.xls --> close a1.xls
...
Read C:\test\a2.html --> paste all content to a new workbook--> save the excel file name as C:\test\a2.xls --> close a2.xls
..
Read C:\test\a3.html --> paste all content to a new workbook--> save the excel file name as C:\test\a3.xls --> close a3.xls

until finish the export of last html file?

Thanks a lot!!!

akanchu
08-17-2008, 09:02 AM
Hi,
Not thinking deep into it.. just my initial thought:

(a) You could use the filesystem object in excel and loop through each HTML file. Check "Files Collection" in vba help in excel. There is an example to browse the objects/files in a folder.

(b) Using the File object, not sure which option would work for you
(i) Use File Open and open the html
(ii) Use Import External data.

(c) Once the file is open, apply a SaveAs with .xls extension

Hope this helps.