Export worksheets from multiple workbooks into pipe delimited txts

[anshul03]

Hi,

I am new to this forum and am hugely impressed by the skill level of the experts here at VB. I am currently stuck by a problem with details as below. I need to develop an Excel 2007 macro to:
- Ask the location of the folder where approx 40 excel files are located (each excel workbook has a variable no. of worksheets; 120 worksheets overall across all workbooks)
- Open each excel file one by one and for each worksheet identify the used cells and export the worksheet into a double pipe delimited text file with the name of each text file same as that of the worksheet
Please note that
- each worksheet needs to be exported to a separate text file
- within the destination folder, all the text files for a particular workbook need to go into a sub-folder by the name of the workbook (this sub-folder might need to be created by the macro)
- there are a few hidden worksheets in each workbook and I would not want them to be exported to text files
- there are a few hidden rows within the used area of each worksheet and I would not want them to be exported to text files

I have been trying to work on this for quite a few days by utilizing the info available on different posts. But everywhere I have been able to find macros that address only different parts of the problem and it has not been possible for me to marry those parts. Any urgent help on this shall be hugely appreciated.

Thanks,
Anshul

 

[Tinbendr]

Try this