Using VBA in Excel97 to launch/run/export a BO report. 4

[AndrewDickens]

Hi All,

I've been able to get BO VBA to automatically export a report to a .txt file after it's refreshed. (The output is used in Excel97)

Now I want to use a VBA macro in Excel97 to open BO and run the report and export it.

From the posts I've checked out, gave some VBA code to use in VBA applications, the first line being:

Dim BOApp As Busobj.Application
.
.

Now Excel stops right there, saying:
"Compile Error:
User defined type not defined"

Q1. Can you run BO from Excel???
Q2. What does this error msg mean???

cheers
AndyD

 

[FunkMaster]

Hi Andy this below is the VBA script that i use to open run and export and import all from excel spreadsheet. What version of BO are you using , i had troubles with 4.1.3 until the Busobj.tlb in the BO folder was changed.But i have no idea why

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&quot
Buso.Interactive = True
Buso.Visible = True
AppActivate "BusinessObjects"
Buso.Documents.Open ("C:\test\report.rep&quot
Buso.ActiveDocument.Refresh
Set HT = ActiveDocument.Reports.Item(1)
Call HT.ExportAsText("C:\test\results\text\report.txt&quot
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&quot
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

' the BOMO085_INV1 report

Workbooks.OpenText FileName:="C:\test\results\text\report.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

Cheers

FunkMaster

 

[pabloj]

You have to reference the object in your VBA project before using it. Go to Tools -> References -> (locate the object) -> OK
Stick to your guns

 

[FunkMaster]

Oh yes and that, opps, I keep forgeting that bit.

The Tea-Boy

 

[AndrewDickens]

Cheers to both of you, this has answered my query perfectly.

Regards,
AndyD

 

[Benouche]

Thanks to you three !

This is a very interesting subject !

In this area I've another question:
what append if you replace Excel by a navigator html.

Is it possible to do the same (running bo and refresh and export ...) from Internet Explorer (I don't see any VBEditor there .. )?

Thanks ,

Benouche

 

[pabloj]

From IE on your PC? Or WebI is too expensive ;-)? Stick to your guns

 

[FunkMaster]

The code below was not written by me , but i think its what you need to export it as an HTML ( notice the object exportasHTML)


Sub RunReports()
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&quot
Buso.Interactive = True
Buso.Visible = True
AppActivate "BusinessObjects"


Buso.Documents.Open ("P:\1998\Meterops\ReportsTeam\Team\Andy\126-PG_FUNC_CHANGE_1PH1R.rep&quot
Buso.ActiveDocument.Refresh
'Set DP = ActiveDocument.DataProviders.Item(1)
Set HT = ActiveDocument.Reports.Item(1)
Call HT.ExportAsHtml("p:\MOS Reporting\ammy\mos\HTML\PRD\PRD", True, True, True, True, True, True, 1, 0, 1)
'Call Buso.ActiveReport.ExportAsHtml("h:\temp", 1, 1, 1, 1, 1, 1, 1, 1, 1)
Buso.ActiveDocument.Save ("P:\1998\Meterops\ReportsTeam\Team\Andy\126-PG_FUNC_CHANGE_1PH1R.rep&quot
Buso.ActiveDocument.Close


Buso.Interactive = True
Buso.Quit
Set Buso = Nothing
Set DP = Nothing
Application.Interactive = True
Application.DisplayAlerts = True
End Sub
The Tea-Boy