Business objects code to save a report called from excel

[asylum]

HI

have some code that will fire up BO and refresh a query, however from there I want BO to simply save the results as a .XLS file

Here is the code so far, what do i need to tack on the end?

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

 

[mdavide]

HI,
I don't know i understand very well what you want (create a new xls file or only paste data inside an open wb) but if you paste the lines below at the end of your code, the routine allows you to paste the data, that are inside report n°1 of your BO document, into a sheet

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)

Dim querys As QueryTables
Dim query As QueryTable
Set querys = ws.QueryTables

Call Doc.Reports(1).ExportAsHtml("D:\query", True, True, True, True, True, True, True, 0, 3)
Set query = querys.Add(Connection:="URL;file:///D:/query.htm", Destination:=ws.Range("a1"))

With query
.Name = "query_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        .Delete
End With

BOApp.Quit

Set DataProv = Nothing
Set Doc = Nothing
Set BOApp = Nothing
Set query = Nothing
Set query = Nothing

ciao

DM