BusinessObjects Board

BO passing data to excel via VB code in excel - too slow!!

sdk development
#1

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

asylum (BOB member since 2006-04-05)

#2

Instead of looping through Data provider, save data provider as CSV or XLS and then create a new instance of Excel application in VBA.

With VBA you can copy shets from this new excel to existing excel.

shamit (BOB member since 2004-07-01)

#3

OK thanks,

Any ideas of the codes required?

Cheers

asylum (BOB member since 2006-04-05)

#4

I attached a document to this post that describes using Reporter as a data source for Excel and Access … including VBA code to automate the process.

{note as moderator … moving this topic from General Discussion to the SDK forum}

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)