Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Large Data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Large Data
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? -- Regards, Nigel wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Large Data
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Large Data
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Large Data
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Large Data
Cos the source is SSRS not Excel !!
-- Regards, Nigel wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Large Data
On Feb 27, 2:07 pm, "Nigel" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to export large amounts of text data into single reports | Excel Discussion (Misc queries) | |||
Best way to easily export large amount of data from Excel | Excel Discussion (Misc queries) | |||
Import Data / Edit Data / Export Data in Excel | Excel Programming | |||
Data too large for Excel, need to query Access data for results | Excel Programming | |||
Fast export large 2-dim array to worksheet | Excel Programming |