Rod Wing


I have a working Excel vba that imports about 50,000 cell values from a variety of different text files into numerous worksheets. The problem is it is painfully slow, taking over an hour to run.

The main time killer is populating each individual cell using the format: Worksheet.Cells(row,col).Value = strValue

Commenting out these lines of code the program runs in under a minute. I've been searching for other alternatives but nothing I've found executes any faster. Ideally I would like to populate one row at a time using a delimited string, or string array.

Other options I've considered but haven't done yet are creating a .csv file for each sheet then importing that, or using ADO from an external program.

I'm looking for any suggestions on how to speed up this application.

Thanks.

Rod Wing




Re: Excel import

Duane in Japan


I am probably not the one to answer your question my friend but is all of your code too long to post, can we get a better feel for what you are importing, readable text (sentences), numbers that are previously formated as text (string on a .doc), words that will populate your xlcells one at a time

I use an external Macro Recorder to do exactly the same task over and over, it also clicks on the screen at specific X and Y coordinates, to copy data from a .doc, paste it in the next previously selected xlcell, I dont think it will do your work in under a minute though and I would like to work on your VBA needs first if possible. I am only a beginner, still, been struggling with VBA for some time.






Re: Excel import

Shasur

Here is the sample for Text Import

Code Snippet

Sub TXT_QueryTable()

Dim ConnString As String

Dim qt As QueryTable

ConnString = "TEXT;C:\Temp.txt"


Set qt = Worksheets(1).QueryTables.Add(Connection:=ConnString, _
Destination:=Range("B1"))

qt.Refresh


End Sub

Cheers

Shasur







Re: Excel import

Rod Wing

Thank you Shasur.

With as quick as that referesh is I think my solution will be to generate a csv file, then import the file using a QueryTable connection.