Business objects code to save a report called from excel

asylum

Board Regular
Joined
Dec 2, 2003
Messages
243
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mdavide

Board Regular
Joined
Oct 19, 2004
Messages
211
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

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

Forum statistics

Threads
1,144,689
Messages
5,725,784
Members
422,640
Latest member
KazPL

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top