BusinessObjects Board

Buisness Objects to Excel.

sdk development
#1

I am using the following code to export the data from the data provider to excel file. While scheduling the report on the BCA i am selecting this as custom macro. The report is scheduled to run daily.

The Problem is that the report runs fine the first time and creates a excel file with the data. When it runs next time it deletes the previous file but does not create a new one.


Sub Document_ExportCSV()

    Dim bodoc As busobj.Document
    Dim boDP As busobj.DataProvider
    
    Dim strOutputFileName As String
    
    Set bodoc = ActiveDocument
    Set boDP = bodoc.DataProviders.Item(1)
    
 
    strOutputFileName = "\\ shared drive name\Business Objects\Excelfile.xls"
    
    intReturnCode = boDP.ConvertTo(boExpExcel, 1, strOutputFileName)
    
End Sub

JaiGupta (BOB member since 2002-09-12)

#2

Looks like you need to add a type of time/date stamp to your filename…

JennFisher :us: (BOB member since 2002-06-25)

#3

Do a search on Exporting to Excel. There are plenty of examples, including information on how to kill the previous file.

-RM

digpen :us: (BOB member since 2002-08-15)

#4

You are not deleting the Excel file anywhere in your code :? So, it should simply overwrite the Excel sheet. When I tried your code, the same happened!! Then, why it is different for you? Is it b’cos of BCA :roll_eyes:

If the Excel file is open, then it will give error!!

If you are interested in the report itself, rather than DP, you can try this logic :slight_smile:

Dim x As Boolean

Filename = “C:\XXX\default.xls”
x = rep.ExportAsText(Filename)

Our reports(may be Excel sheets) are not behaving strange with this…

Ravi Amara :us: (BOB member since 2002-10-02)

#5

I have already searched the BOB but with not much succes. I ran the macro on desktop and it gave me an error saying that file is already open. Would you please send me the code to kill the existing file. The requirement is to overwrite the existing file each time the report runs on the BCA.

JaiGupta (BOB member since 2002-09-12)

#6

I would be glad to provide the exact code but it is already avl in the code samples section…

If Dir(Path &amp; ExcelDoc &amp; ".xls") <> "" Then 
   Kill Path &amp; ExcelDoc &amp; ".xls" 
End If

avaksi :us: (BOB member since 2002-08-22)

#7

Thanks I got the code…

JaiGupta (BOB member since 2002-09-12)