Metadata update BI4 vs Sharepoint

With the implementation of BI4, our company moved from producing .xls files to .xlsx files. All good and well, but that brings some unexpected consequences as well.


Every day, we produce some 80 reports, that get stored on a SharePoint Server. Every file has some custom properties in SharePoint, like a periodicity and a Group.


With .XLS files, this metadata gets stored on one place only : SharePoint. However, when you work with .XLSX files, you may run into some trouble -- because the Metadata is then stored INSIDE the document. Given the fact that some of the properties are custom, there is no way to update the metadata, until you have actually uploaded the document to SharePoint -- and if that file came from Business Objects, you just overwrote your Metadata with BLANKS.


This article will provide you with a work-around.


=========================


First some VBScript code:


       Set oBook = oExcel.Workbooks.Open(objFile)
       oBook.saveas strDestFolder & Replace(objFile.Name, "$", "\"), 51
       oBook.ContentTypeProperties("Group").Value = sGroup
       oBook.ContentTypeProperties("Period").Value = sPeriod
       oBook.ContentTypeProperties("Description").Value = sDescription
       oExcel.AutomationSecurity = 1
       oBook.save
       oBook.close
       oExcel.quit


Before you implement this, you will be needing to write a script that will copy your Excel documents to SharePoint. If you need help with that part, probably the above code will do you little good.


It took me a good deal of time, to write the code above -- very happy with it. What I'm doing in the rest of the script is run through a text-file to locate the file I'm copying --and I store the current description and group in it.


The script above will then store that onto the SharePoint server, after saving the file to that location.


For you Powershell lovers:


$objFile = $objExcel.workbooks.open($OutFile)
            $builtinProperties = $objFile.ContentTypeProperties
            foreach($builtinProperty in $builtinProperties){
                if ($builtinProperty.Name -eq "Description"){
                    $builtinProperty.value = $Description
                }
                if ($builtinProperty.Name -eq "Period"){
                    $builtinProperty.value = $Period
                }
                if ($builtinProperty.Name -eq "Group"){
                    if($group[1] -eq "B00"){                    
                        $builtinProperty.value = "Bank"
                    }
                }
           }
           $objFile.Save()


I have no good PowerShell editor, so the above code took me even longer to put together.. it does the same as the vbscript.


So the workaround is basically : use VBScript to copy your Excel file to SharePoint. Open it with VBScript, read the metadata from a textfile.. and then, using the code above, to set the metadata.


I hope it helps you -- I'm pretty sure it will help me, if I ever need to figure this out again.


Peter

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

 

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

Business Objects Scripted refresh

a student gave me the following script to automate refresh of a report :

Sub RefreshBO()
Dim objBO, objrep
Application.DisplayAlerts = False
'Open Business ObjectSet objBO = CreateObject("BusinessObjects.Application")
' (when update to version 6)' Set objBO = CreateObject("BusinessObjects.Application.6")
objBO.LoginAs "username", "password", False' (fill in username and password)
'open the report
objrep = objBO.Documents.Open("c:\myfile.rep")

'Show BO
objBO.Visible = True

'Refresh Report
objrep.Refresh

'Save Report
objrep.Save

'Close Report
objrep.Close


great !!