StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>One possibility would be to make it a two-stage process but it depends on where the bottleneck is.</p> <p>If it's Excel, then just turn your recordset rows into a CSV-type file and then, when that's done, create the Excel object and import the entire file into a fixed location.</p> <p>That's probably going to be faster than a cell-by-cell operation.</p> <p>If you can't import CSV into a fixed location on the sheet (ot the cells are not in consecutive rows or columns), I'd import the CSV onto a new sheet, then do mass copies from there to your template sheet.</p> <p>Moving ranges should also be faster than cell-by-cell operations.</p> <p>It's the bulk import and mass copy that should give you some good improvement. I've had worksheets that processed individual cells that sped up by many factors of 10 when you use the more complex Excel functionality (think using =sum(a1..a999) instead of adding up each individual cell in VBA and putting that value somewhere).</p> <p>As to how to do the import from VBA, I always rely on the <code>"Record Macro"</code> feature to get a baseline which can be modified (for those I'm not intimately acquainted with). This one imports <code>c:\x.csv</code> into the current sheet at <code>C7</code>:</p> <pre><code>With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\x.csv", _ Destination:= Range("C7")) .Name = "x" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With </code></pre> <p>Now I'm sure most of that rubbish in there could be deleted but you'd be wise to do it one at a time to ensure no problems show up.</p> <p>You could also modify that with something like the following to use a different sheet.</p> <pre><code>dim ws as worksheet dim savealert as boolean set ws = Sheets.Add ws.select ' Put all that other code above in here. ' ' Move all that data just loaded into a real sheet. ' savealert = Application.DisplayAlerts Application.DisplayAlerts = False ws.delete Application.DisplayAlerts = savealert </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload