Using VB in Excel to open, refresh and export data

[aarondewberry]

All I am trying to use VB in excel to open, refresh and export data from a BO 5.1.9 report.
I am using code from the following thread thread393-379294
The problem comes with the Buso.Documents.Open part of the code. It keeps coming up with an error message "You are not authorised to use this document". I have a password and username for BO 5.1.9, is this what is affecting the code? If so, how do we get around it?
Code
Dim Buso As busobj.Application
Dim DP As busobj.DataProvider
Dim HT As busobj.Report
Dim HP As busobj.Document
Dim I As Integer
Application.Interactive = False
Application.DisplayAlerts = False
Set Buso = CreateObject("BusinessObjects.Application")
Buso.Interactive = True
Buso.Visible = True
AppActivate "BusinessObjects"
Buso.Documents.Open ("C:\test\sking05.rep")
Buso.ActiveDocument.Refresh
Set HT = ActiveDocument.Reports.Item(1)
Call HT.ExportAsText("C:\test\results\text\sking05.txt")
Buso.ActiveDocument.Save
Buso.ActiveDocument.Close

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

' the BOMO085_INV1 report

Workbooks.OpenText Filename:="C:\test\results\text\sking05.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Application.Interactive = True
ActiveWorkbook.SaveAs Filename:= _
"C:\test\results\excel files\report_INV1.xls", FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close

 

[hilfy]

You are not authorised to use this document" means just that. Just because you have access to BO does not mean that you have access to the specific report you're trying to access. Check with you BO system admin to see whether you have access to the report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[aarondewberry]

I actually built the report and I run the report on a daily basis. The code opens BO fine, it just can't get past the log on screen!

 

[blom0344]

You can directly start a BO session from the command line, which bypasses the logon screen, like (example):

c:\Program Files\Business Objects\BusinessObjects 5.0\busobj.exe" -user Test -pass Test

I can imangine you can execute this from the VBA script?

Ties Blom

 

[xlbo]

This is a standard bit of code I use to open a BOBJ report and copy data to excel. As you can see, you need to provide a username and password (unless you want to go the command line route):

Sub GetBOData()
Dim BoApp As busobj.Application, BODoc As busobj.Document, BORep As busobj.Report
Set BoApp = CreateObject("BusinessObjects.application")
With BoApp
    [b].LoginAs "username", "password"[/b]
    .Visible = True
    .Documents.Open ("FilePath\FileName.rep")
    With .ActiveDocument
        .Refresh
        i = 1
        For Each rpt In .Reports
            rpt.Activate
            BoApp.CmdBars(2).Controls("&Edit").Controls(20).Execute
            Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues
            i = i + 1
        Next
    End With
End With

Set BoApp = Nothing
Set BODoc = Nothing
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question