Simulate paste action in excel using vba

Question

Hi,

I am trying to populate an excel spreadsheet using VBA (is actually something different using a COM server but it is the same syntax). Thisis a report generated that I want to write in Excel for formatting and presentation purpose. CSV exports and other techniques are out of the equation because of the technology involved.

So, I am able to do it writing each value in each cell, but it is painfully slow, so, I decided I could write a bunch of values at once (let's say 250 records each time). So I store those values delimited by a carriage return and if I copy/paste that using VBA it works perfectly.

Problem is, I can't use the clipboard (as the report is executed many times simultaneously), so can I simulate the paste action (or some other similar) from a list of values in Excel without using the clipboard?

Thanks,

Answer 1

The fastest way to copy values in VBA I know is

Range("destrange") = Range("srcrange")

destrange and srcrange should be the same size. Also no formatting data etc. will be copied.

edit: here is something that could be usefull.

Answer 2

You can create an array of Variants and assign it to a range:

  Dim v() As Variant

  ReDim v(1 To 20, 1 To 10)
  'Fill v
  v(5, 5) = 42
  '...

  Range("a1:j20").Value = v