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
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