Export to Excel Macro (Businss Object 5.1.1)

Some times we need to work with old versions like I was working with BO 5.1.1 and I was missing export to Excel functionality. I created below macro to export BO report to Excel.

This Macro is created in Excel. So Copy this macro in Excel and add Business Object references before running the macro. Excel and Business objects should be installed in same machine.

Sub ExportToExcel()
On Error Resume Next

Dim app As New busobj.Application
Dim doc As Document
Dim Temptxtfilename As String
Dim Textfilename As String
Dim Counter As Integer
Dim myReports As Reports
Dim ws As Worksheet

'It will ask for temp file name. This will be used for exporting BO report to textfile
Temptxtfilename = Application.GetSaveAsFilename
Textfilename = Mid(Temptxtfilename, 1, InStr(1, Temptxtfilename, ".", vbTextCompare) - 1)

'Now we will open Business Objects, It will ask you username and password
app.Visible = True
app.Interactive = True
app.LoginAs

'Now It will ask you to open the Business Objects document which you want to export in Excel
Set doc = app.Documents.Open

For Counter = doc.Reports.Count To 1 Step -1

'Export Individual report to text file
Temptxtfilename = Textfilename + "(" + doc.Reports.Item(Counter).Name + ").txt"
doc.Reports.Item(Counter).ExportAsText Temptxtfilename

'Add a worksheet in existing Excel
Set ws = ActiveWorkbook.Worksheets.Add
ws.Name = doc.Reports.Item(Counter).Name

'Import data from text to excel
With ws.QueryTables.Add(Connection:= _
"TEXT;" + Temptxtfilename, Destination:=Range( _
"A1"))
.Name = doc.Reports.Item(Counter).Name
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next

'Closing of document and Business Objects application
doc.Close
app.Quit

'Excel is still open so you can save it

End Sub