Export Large Data

[kakani.ashish]

Hi,
We are using SSRS to generate the reports. We want to export the data
from SSRS control to excel but we need to built different formula and
graphs. We are getting timeout exceptions when exporting large amount
of data to excel through our custom program. we are writing row by row
data in the excel file which i guess is causing performance hit. But
we need to write row by row because we are building some formulas in
the cell.

Does anybody knows the better way of exporting to excel rather than
writing row by row? or anyother way by which the performance can be
improved so that we dont get timeout?

Thanks in advance...

Regards,
-Ashish

 

[Nigel]

Maybe the wrong NG for this request? But why build the formula row by row
as you import data, try loading all data and then apply and copy formula?

 

[gimme_this_gimme_that]

Hi Ashish,

You can build an array of array and stuff the data into the Worksheet
in one instruction.

The values 2,4,8 and 2,8,11 are values I cooked up to so you could
see a working example.

You'd replace these values with values coming from your data source -
like a DAO ResultSet.

Note that you might run into performance problems if you're inserting
20000 rows at a time - because VBA will be keeping all that data in
memory.

So you might find it better to burst the data into the sheet 100 rows
at a time.

YMMV


Sub test()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
Dim ar() As Variant
s.Activate
ReDim Preserve ar(1 To 1)
ar(1) = Array(2, 4, 8)
ReDim Preserve ar(1 To 2)
ar(2) = Array(2, 8, 11)
s.Range("A1:C2").Value = Application.Transpose(Application.Transpose
(ar))
End Sub

Sub test2()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
Dim ar(1 To 2) As Variant
s.Activate
ar(1) = Array(2, 42, 8)
ar(2) = Array(2, 84, 11)
s.Range("A1:C2").Value = Application.Transpose(Application.Transpose
(ar))
End Sub

www.gearyandpowell.com

 

[gimme_this_gimme_that]

Nigel, because copying and pasting *large* amounts of data in VBA is
slow and can make your VBA application unstable (it might freeze).

What do you mean this isn't the right news group. If this isn't what
is.

 

[gimme_this_gimme_that]

Oops. Your question was about exporting - not importing.

What exactly are you doing now?

Are you using VBA to write the values to a text file?

What about the option of looping and writing the data to a database?

If you're copying and pasting - that's why you're timing out.

 

[Nigel]

Cos the source is SSRS not Excel !!

 

[gimme_this_gimme_that]

Cos the source is SSRS not Excel !!

I thought the OP said they had control over how data was written - row-
at-a-time.

I don't know anyting about SSRS but if SSRS gives him access to an
Excel WorkSheet then he should be able to write the data all at once -
like I showed him.