Scripted BO 5 excel export

 

A customer asked me to make the following script -- some time at google and some writing later, here's what I put together. It takes all the files in a given directory and converts all the BO reports into excel files (no charts though) each tab in each report is of course copied into the appropriate excel file.

Use with care..

'----------------------------------------------------------------------

dim busobj
dim strnomFichier
dim objrep
dim objExcel
dim boEditPopup
dim xlworksheet
dim strname
Dim BOApp
Dim strFilename

set BOApp= createobject("BusinessObjects.Application")
BOApp.LoginAs "USER", "PASS", False
BOApp.visible = True

Set fso=Wscript.CreateObject("Scripting.FileSystemObject")
Set f=fso.GetFolder("i:\")
Set fc=f.files

For each file in fc
    strFilename = file.name
    If Right(strFilename,3) = "rep" Then
    Set objrep = BOApp.Documents.Open("i:\"&strFilename)
    Set boEditPopup = BOApp.Application.CmdBars(2).Controls("&Edit")
    Set objExcel = createobject("Excel.Application")
    objExcel.Workbooks.Add
        objExcel.visible = True
        intreports = 1
        startnumber = BOApp.ActiveDocument.reports.count
     For  i = startnumber To 1 Step -1
        Set myrep = BOApp.ActiveDocument.reports.item(i)
        myrep.activate()
        boEditPopup.CmdBar.Controls("Cop&y All").Execute

        Set xlWorkSheet = objExcel.Worksheets.Add()
        strname = BOApp.ActiveDocument.reports.item(i).name
        strname = Replace(strname, ":", "") ' Can't contain this character
        strname = Replace(strname, "\", "") ' Can't contain this character
        strname = Replace(strname, "/", "") ' Can't contain this character
        strname = Replace(strName, "?", "") ' Can't contain this character
        strname = Replace(strName, "*", "") ' Can't contain this character
        strname = Replace(strName, "[", "") ' Can't contain this character
        strname = Replace(strname, "]", "") ' Can't contain this character
        strName = Left(strname, 31)
        xlWorkSheet.Name = strname
        xlWorkSheet.Paste
        Set xlFormatPopup = objExcel.Application.CommandBars(1).Controls("F&ormat")
        Set xlColumnPopup = xlFormatPopup.CommandBar.Controls("&Column")
        xlColumnPopup.CommandBar.Controls("&AutoFit Selection").Execute
     Next
     End If
     If Right(strFilename,3) = "rep" Then
         xlWorkSheet.Saveas "i:\"&strFilename&".xls", True
     End If
     Next

 

'-----------------------------------------------------------------

My new laptop

At the bottom of one of my cupboards, I found my old Toshiba laptop. It isn't much, it's got some ram.. not a lot.. some diskspace, not a lot.. and it's got Windows XP.

As a decent computer user, I prefer to only put legal software on my computer. Of course, that software has to function correctly and give me the features that I need.

I'm not a rich man, so I decided to go for the Open Source software that's around.

The first thing I decided on installing was office. I'm not planning to spend too many of my precious Euro's on office -- I won't be using it much on this laptop -- only to consult files that I receive from people mostly.

Open Office -- of course. A swift download, an install, and I have Spreadsheet, Wordprocessor, Image editing soft -- the lot.

Site : http://www.openoffice.org/

So.. what other software do I need. I develop websites -- so I need a webserver -- a database server --  content management -- blogs.. the choice is quite easy : MoWes (fka WoS)

Site : http://www.chsoftware.net/en/useware/mowes/mowes.htm

Great -- now I have a complete development environment with PHP, MySQL and all the nice stuff that comes with it -- but how will I be writing my webpages..

I searched the web over and found a long list of indivual tools like : NVU, Aptana, Selida -- none come close to a decent webdesign tool -- Aptana is coding -- I'm not planning to code my pages by hand if I can help it. Selida offers layers, but you can't do something simple like drag the layer to the desired location..

Site : 25 webdesign tools

A browser. I love Firefox, but the almost nihilistic interface of Chrome got me. Especially because in chrome, you can save a shortcut to a website on your desktop or in a folder. When you open the icon, you get that website in a window without navigation -- in practice, it looks like a windows program has been opened and not a website.

Site : http://www.google.com/chrome

Now I have my e-mail as an icon on my desktop -- I surfed to GMail with Chrome and saved the site as an icon -- it looks and acts like a windows program, with an important downside -- you have to be online to read your mail.

I did the same with the google calendar, google Picasa and google notepad. To store my office documents in a place where I can always reach them, I have also added Google docs -- easy.

site : www.google.com

I blog. So a decent blogging tool would come in handy : Windows Live. It contains LiveWriter in which I'm writing this article, live mail.. live messenger -- Live mail will come in handy to read those mails that are not accessible over a website.

site : http://get.live.com/writer/overview

Next Month

function GetLastDayNextMonth()
Dim dt
Dim firstDay
Dim lastDayNextM
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastdayNextM = DateAdd("m",2,firstDay)
lastday = DateAdd("d",-1,lastdayNextM)
GetLastDayNextMonth= lastDay
end function
function GetFirstDayNextMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayNextMonth()),month(GetLastDayNextMonth()),1)
GetFirstDayNextMonth= firstDay
end function

Can’t believe I wrote that function.

=dateadd(“d”, –1, dateadd(“m”, +2, cdate(year(today()) & “/” & month(today()) & “/1”)))

that’s the expression that gives you the last day of the next month in Reporting Services

Calculating Current Month

function GetLastDayNextMonth()
Dim dt
Dim firstDay
Dim lastDayNextM
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastdayNextM = DateAdd("m",1,firstDay)
lastday = DateAdd("d",-1,lastdayNextM)
GetLastDayNextMonth= lastDay
end function
function GetFirstDayNextMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayNextMonth()),month(GetLastDayNextMonth()),1)
GetFirstDayNextMonth= firstDay
end function

Installing BOXIR3

The installation of Business Objects XI R3 seems pretty straight forward. Double click the setup file.. next next next.. but.

After the installation, when I tried to surf to any of the pages on my Tomcat server, I got Error 404 application not found.

After a bit of surfing, I found an entry on a forum.

It was a discussion between two people that entirely pictured my problem.. what follows is a howto based on their conversation.

When trying to surf to http://servername:8080/CmcApp, I got an error 404 saying CmcApp not found.

1) go to

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\java\applications

and check if the WAR files are there -- if not -- reinstall

2) go to C:\Program Files\Business Objects\Tomcat55\conf and change the tomcat-users.xml file to contain a user (currently logged user f.i.) with rights

like such :

<user username="username" password="yourpassword" roles="admin,manager"/>

don't forget to restart the tomcat server (service)

3) surf to http://servername:port/manager/html (Tomcat Manager) and login with the username and password you just created

4) add all of the war-file (bottom of the page) from the directory you found in step 1.

5) try starting the CMC again

(this worked for me)

Into the blogging thing

I have been blogging a short while now -- just a couple of years. Only now, the whole process seems to be streamlining itself. My first articles, I wrote in the webinterface of the blogging website. harsh -- inspiration does not come at a given time.. so I would gather my stuff before the article and then write it on the site.

Then, I discovered BlogJet -- an easy, straightforward tool to blog. Configure it with login and password.. and go ! A big step forward.

Now, some time later, I'm writing in Live Writer from Microsoft -- and it is a definite step forward. The thing I like most is the fact that you can connect directly to the site -- and it copies the template you are using. this means, that you write the article inside a window -- and it should be what you see is what you get. Furthermore, you can connect to multiple blogs.. just select the right one -- the screen changes to the template of that site -- and go ! the article is published.

I'm a fan of Windows Live Writer.