Re: Code Efficiency Suggestions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 sheet 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 worksheet
Import data from text file using ActiveSheet.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 spreadsheet 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.

.

Relevant Pages

  • Re: Code Efficiency Suggestions
    ... > Outer loop: while not EOF of input file ... > create new output file in the new folder ... > Inner loop: while output file row counter <65000 and not input EOF ...
    (microsoft.public.excel.programming)
  • Re: Steve, have you had any problems with C5LIB.C writing to disk?
    ... an output file for writing under MS-DOS v7.10 in real mode. ... DOSLFN, no path, full path, DOS LOCK command (to allow direct writes to ... 'short cuts' can't be copied with a 'work_space' folder to ... I use 'shortcut's for nasm and sid. ...
    (alt.lang.asm)
  • Re: How To Rebuild Objects From Text Files?
    ... new folder, but stops with the error at the line of code that I posted ... But a general Google search for "can't create the output file 2285" ... And you must have write permissions there or SaveAsText would fail. ...
    (comp.databases.ms-access)
  • Re: listage recursif sans les dossiers systeme
    ... set repParent = racine.subFolders ... For each fichier in fso.GetFolder.Files ... - It is wasteful to open and close the output file all the time. ... i.e. you must specify a drive letter and a folder name. ...
    (microsoft.public.scripting.wsh)
  • Re: Switching with case in perl ?
    ... I tried using it with no success, I modified a little bit and still no ... the loop is working but keeps on looping, Once the outer loop finds ... it gets the logs to the output file and keep repeating ... extracting from the same log file. ...
    (comp.lang.perl.misc)