Open an excel workbook from BO using vba 1

[DrSmyth]

Hi,

I'm exporting some business objects reports to excel using some vba, but i've been a little dissapointed with the formating. Si i've made an excel file that will format my exported reports.

The trouble i've got is that i need to open the excel file from within Business Objects using vba (this is an automated process)

Does anybody have any code that i can use?

 

[xlbo]

Set your references to excel in the VBE and this should get you going:

Sub OpenExcel()

Dim objExcelApp As New Excel.Application
Dim objExcel As Object
'create Excel
Set objExcelApp = New Excel.Application
'open file
Set objExcel = objExcelApp.Workbooks.Open("D:\booknew.xls")
objExcelApp.Visible = True
'Do stuff here
Set objExcel = Nothing 'clears memory

End Sub

Another way of doing it is to control the whole lot via excel eg:

Sub GetBOData()
Dim BoApp As busobj.Application, BODoc As busobj.Document, BORep As busobj.Report
Set BoApp = CreateObject("BusinessObjects.application")
With BoApp
    .LoginAs "Username", "password"
    .Visible = True
    .Documents.Open ("Report Path & Name.rep")
    With .ActiveDocument
        .Refresh
        i = 1
        For Each rpt In .Reports
            rpt.Activate
            'use the copyALL function
            BoApp.CmdBars(2).Controls("&Edit").Controls(20).Execute
            'paste to excel
            Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues
            i = i + 1
        Next
    End With
End With
'set variables to nothing
Set BoApp = Nothing
Set BODoc = Nothing
End Sub

Rgds, Geoff
[i][blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue][/i]
Want the [b][red]best[/red][/b] answers to your questions ? faq222-2244

 

[DrSmyth]

Cheers xlbo, once again you've come to the rescue

 

[DrSmyth]

XLblo, hi, have tried to include your first piece of code, my code now looks like:

Sub exporttoexcel()
Dim doc As Document
Dim rep As Report
Dim objExcelApp As New Excel.Application
Dim objExcel As Object

Set doc = Application.Documents.Item(1)
Set rep = doc.Reports.Item(1)
rep.ExportAsText ("F:\Caseholders\Caseholder Reports\Sols Not Instructed" & ".xls&quot

Set objExcelApp = New Excel.Application
Set objExcel = objExcelApp.Workbooks.Open("F:\MI\Weekly\Clenup xls for Sols Not Inst.xls&quot
objExcelApp.Visible = True
Set objExcel = Nothing
End Sub

But i've also tried running your code as is, the problem is that when i get to the line

Set objExcel = objExcelApp.Workbooks.Open("F:\MI\Weekly\Clenup xls for Sols Not Inst.xls&quot

i'm getting a error 13, type mismatch, error.. Any ideas what this could be?

 

[xlbo]

shouldn't be a problembut try changing
Dim objExcel As Object
to
Dim objExcel As Excel.Workbook


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244