I have created a sub to import tab delimited files into a specific sheet. Everything works but it is so horribly slow it is unusable. The files I am importing will start now with around 4-5000 lines and will grow every day at a rate of approximately 5000 lines per year. Right now it is taking around 27 seconds to import 5 lines. Each line has 7 tab separated values. If I take out the single line that actually puts the value in the cell the code goes through the entire file almost instantly.
Just looking to see if there is something I can do to make it faster. I have tried this in Excel 2007 on Windows, and Excel 2011 for Mac with the same results.
Here is the entire sub. I have comments in there to show where it is slow.
This code is being called in the Workbook_Open() event. Not sure if that matters or not.
Please Login or Register to view this content.
Here is a sample couple of lines of the data in the text file.
65089 4/8/2011 3/9/2011 550321187 DELPHI ELECTRONICS & SAFETY 456 DIC
65088 4/8/2011 3/9/2011 550321449 DELPHI ELECTRONICS & SAFETY 1682 DIC
65087 4/8/2011 3/9/2011 550321449 DELPHI ELECTRONICS & SAFETY 234 DIC
65086 4/8/2011 3/9/2011 100655-001 "The AVC Group, LLC." 1382.65 DIC
65085 4/8/2011 3/9/2011 60272 DYNA GRAPHICS CORP 174.3 DIC
I am also attaching a partial file of the data to this post "tabdata.txt".
There is a timer in there that will pop up a message box every 5 lines it imports.
I'm not sure if there is any way to do this faster. I have searched the forum for import but nothing that I have found makes any sense to me how to improve this.
Any help is greatly appreciated!