Macro Optimization

i have a macro that loads 250 txt files (inside a loop) and do some things with them.
im using this code to open the file, is there any way to open all the files in the same workbook (now i see an excel window appearing and dissapearing 250 times in taskbar). I think it can speed up a little my macro.

Please Login or Register  to view this content.

I'll better explain what I do, i open that workbook from a TXT select ALL and paste it in one of the 12 sheets of my workbook (starting from A2 because i have the titles in the row 1). Is there any way to load the files in one step the there or the copy paste i do is necesary?

thanks and sorry for my english

Alex

hi Alex

Try using this to stop the screen from flickering as your code runs:

Please Login or Register  to view this content.

James

im using that and doing the calculations at the end of the macro, i only want not to open 1 excel instance for each file
thanks

Ok

I assume then that the problem is that you end up with 250 files open at the end, then, rather than all files being appended to one worksheet.

Looking at help on OpenText it seems to create a new workbook by default:

"Loads and parses a text file as a new workbook with a single sheet"

The Open method of the Workbooks collection has the same effect. I feel that you'll need to program a combining routine of your own.

There are some other optons worth investigating:

You could try importing the files using Query Table object, rather than opening them as files.

Personally I would always try to get external files built as CSVs and appended prior to running Excel.

Thought: do you know how to run a DOS Batch file to combine the files before opening them (as one text file)?

James

Thought: do you know how to run a DOS Batch file to combine the files before opening them (as one text file)?

I know how to do that, but now each file have like 10k lines...there are 250 files... 2.500.000 lines its a little too much.
I end up with only one excel open (the one with the macro) but a new one open to copy the content to the first one.
I want to load the file to a sheet in my first excel starting from A2, if that cant be done, then I want to keep the other excel open and work loading and clearing 1 sheet.
If the first thing can be done it will improve the performance, if not, i think the second will do something too.
sorry about my english
thanks again

so let me get this right . . .

you want the macro (which is stored in Workbook1) to:

0. Set the TOP as cell A2
1. open the TextFile1 as Workbook2
2. select all data in Workbook2
3. copy all data to clipboard
4. activate Workbook1
5 select Workbook1 at TOP
6. paste the data

a. if the paste doesn't fit on the sheet, move to A2 on the next sheet

b. do step 6 again]

c. find the last row of the pasted data]

d. set TOP as the next row down]

7. activate Workbook2
8. close Workbook2
9. open textfile 2 as Workbook2
10. repeat steps 1-8
11. open textfile 3 as Workbook2
12. repeat steps 1-8
..... for each of 250 text files


Is this how it should work?

in that way is working now, i want to optimize it...if there any way, I think that open a new workbook and copy/paste large amount of data take a while :S.

Whilst I'm still not clear on the nature of the data, the processing or the outcome, it seems that if you're dealing with 10,000 lines per file and 250 files, Excel may not be the best option.

For example, I often use Microsoft Acces to process data downloaded from our iSeries (AS/400) warehouse, because the transactions won't fit in Excel.

Access has the capacity to efficiently processing of these quantities of data, and importing text is not usually a problem, as the file definitions can be stored and used repeatedly on different files.

If this is outside your experience, it might appear daunting. I can assure you that once you get a handle on it, its very powerful. Also, understand that Access is only one of a miriad of database systems available. There may be one more suitable to your current systems. For example, our IT gurus often use MySql, which is OpenSource, and thus basically free.

The only other thing I can suggest in Excel is checking out the QueryTable object, that seems to allow you to access data in a foreign file (like a text file). I've personally had no experience with it.

Otherwise, without seeing your code, it's difficult to optimize the porcess.

James

I'll think of using access but im not the one taking desitions there :P, the excel has 12 sheets dat do things with the data copyed from that workbook i open per file. If i start using access or another DBMS there will be a LOT of programing involved.
sorry if I use any invented word :P
thanks agani

If you want further help then, you'lll need to provide a copy of the files, or at a minimum the code you are using.

James