Business Objects passing data to excel - very slow - help!

asylum

Board Regular
Joined
Dec 2, 2003
Messages
243
HI. I have a workbook which contains the following code. This opens Business Objects, call a document and refreshes the data, all good so far.

The problems comes when i need to pass this data to excel, which is done in the for - next loop. This works and is fine for small data sets, but is excruciatingly slow for anything more than 50 lines!

does anyone knwo how simply to 'cut' from BO report and 'paste' into the excel sheet, which would be much quicker.

Thanks

Andy


Dim BOApp As busobj.Application
Dim Doc As busobj.Document
Dim DataProv As busobj.DataProvider
Dim i As Long, j As Long

Set BOApp = New busobj.Application
BOApp.Visible = False

Call BOApp.LoginAs

Set Doc = BOApp.Documents.Open("C:\NCRS Audit Tools\NCRS Test One\NCRS T1.rep")
Doc.Refresh
Application.ScreenUpdating = False
Set DataProv = Doc.DataProviders(1)

Sheets("Data").Visible = True
Sheets("DATA").Select

For i = 1 To DataProv.Columns(1).Count
For j = 1 To DataProv.Columns.Count
ActiveSheet.Cells(i + 1, j) = DataProv.Columns(j).Item(i)
Next j
Next i

Doc.Close
BOApp.Quit
Set BOApp = Nothing
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
This is air code and I had no way of testing it. However, with a little finagling, you should be able to get it to work for you.

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> BOApp <font color="#0000A0">As</font> busobj.Application
       <font color="#0000A0">Dim</font> Doc <font color="#0000A0">As</font> busobj.Document
       <font color="#0000A0">Dim</font> DataProv <font color="#0000A0">As</font> busobj.DataProvider
       <font color="#0000A0">Dim</font> i <font color="#0000A0">As</font> Long, j <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
       <font color="#0000A0">Dim</font> TempArray()
      
       <font color="#0000A0">Set</font> BOApp = <font color="#0000A0">New</font> busobj.Application
       BOApp.Visible = False
      
       <font color="#0000A0">Call</font> BOApp.LoginAs
      
       <font color="#0000A0">Set</font> Doc = BOApp.Documents.Open("C:\NCRS Audit Tools\NCRS Test One\NCRS T1.rep")
       Doc.Refresh
       Application.ScreenUpdating = False
       <font color="#0000A0">Set</font> DataProv = Doc.DataProviders(1)
      
       Sheets("Data").Visible = True
       Sheets("DATA").Select
      
       <font color="#0000A0">ReDim</font> TempArray(1 <font color="#0000A0">To</font> DataProv.Columns(1).Count, 1 <font color="#0000A0">To</font> DataProv.Columns.Count)
      
       <font color="#0000A0">For</font> i = 1 <font color="#0000A0">To</font> DataProv.Columns(1).Count
           <font color="#0000A0">For</font> j = 1 <font color="#0000A0">To</font> DataProv.Columns.Count
               TempArray(i, j) = DataProv.Columns(j).Item(i)
           <font color="#0000A0">Next</font> j
       <font color="#0000A0">Next</font> i
      
       ActiveSheet.Cells(2, 1).Resize(UBound(TempArray, 1), UBound(TempArray, 2)) = TempArray
      
       Doc.Close
       BOApp.Quit
       <font color="#0000A0">Set</font> BOApp = <font color="#0000A0">Nothing</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top