Import .csv macro taking too long

[Ctomo]

I have a macro which picks all csv files from a specific folder location and copies a line from that files, imports it into a new sheet whch is looped until data has finished. The problem is because there are alot of strings to collect the macro takes upto 20 mins to complete (if it doesnt stop responding) can anyone help shortening the time? Macro below. Thanks

Please Login or Register  to view this content.

[Andy Pope]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)


Have you tried using the Import feature in Excel. On the Ribbon, Data > Get External Data > From Text.

You can specifiy where the output starts, which will allow you to append files. You will though need to remove fieldname row from subsequent data sets, once imported, if you have one.

[Ctomo]

I have yes Andy, but the problem is the files i import change file names and the data strings can be anything upto 100,000

[Andy Pope]

Not sure I follow. Your existing code handles unknown multiple file names. So you would just alter the import code to do the same thing.
Whats the 100,000 issue?

[Ctomo]

the files i import do not have a set amount of rows they are unknown until they are opened. To be honest I just about managed to write the macro as i know nothing about VBA.

[Andy Pope]

Still not seeing the problem. As long as you are importing all the lines, which I think your code is doing, then no issue.

This code snippet demonstrates how to write out a line of data to cells in 1 go rather than looping.

Please Login or Register  to view this content.

If you need more help then it may be useful to post a few example input files so we can test.

[Ctomo]

It works fine it take a long time and im impatient

i have attached an example of files i use along with the main file. If there is nothing to change then not too worry i can stick with it

[Andy Pope]

Actual the problem is not so much the import of data but the calculations done every time a cell changes.

here is some revised code

Please Login or Register  to view this content.

this is the result of a speed test I did. A reduction of ~97%

Original code. Start 10:22:58 Finished 10:25:45 Duration 00:02:47
New code. Start 10:39:24 Finished 10:39:28 Duration 00:00:04

[Ctomo]

Thanks Andy...i cant seem to get it to work :/ i know i am doing something stupid but i dont know what!

[Andy Pope]

well you don't give me any information about what actual did so it's hard to say.

You simply need to replace that routine. The only change required is the myPath assignment.
I changed it to use the same folder as the workbook. Just delete the second line.

Please Login or Register  to view this content.

[Ctomo]

Can't believe i missed that! As i said, i did (or in this case didn't) do something stupid. You're a star. Thank you very much