Re: Code Efficiency Suggestions

Tech-Archive recommends: Fix windows errors by optimizing your registry

Another option would be to process the data in the background, storing
it in a bunch of text files (65k rows/file), then import each text file
into a new *** using QueryTables. The pseudo code is as follows:



Create new folder

Outer loop: while not EOF of input file
create new output file in the new folder
write field headers to output file
output file row counter = 0
Inner loop: while output file row counter <65000 and not input EOF
read line
transform contents of line
write line to output file in csv format (NO SPACE after comma)
increment output file row counter
End inner loop
End outer loop

For each file in new folder
Select work***
Import data from text file using Active***.QueryTables
Next

Delete files and new folder



I would think that this would much faster since you are pre-processing
the data without having to interface with the spread*** and the Excel
object hierarchy. QueryTables is very fast and works on formatted text
files just as well as it does on databases.

Try recording a macro while doing Data > Import External Data > Import
Data. This will prompt you for a data source. Navigate to the text
file like you're opening a file, and follow the importing wizard.

.

Quantcast