BusinessObjects Board

Export to Excel Macro

sdk development
#1

Can someone please send me a VBA code which does a simple Edit, Copy All from Business Objects & pastes in Excel. It needs to export multiple tabs of Business Objects to multiple tabs in Excel.

Thanks,
Dinesh.

Dinesh (BOB member since 2002-08-22)

#2

You want the Code samples forum check out this link from Dave - our first Forum Enthusiast (1000+ posts!) :mrgreen:

Nick Daniels :uk: (BOB member since 2002-08-15)

#3

Thanks Nick.

I think Dave’s example is great. I need a bit more cusomization. Dave’s code does not cleanup the temporary text & excel file. Can someone give the modified code which does a delete of these temporary files.

Thanks,
Dinesh.

Dinesh (BOB member since 2002-08-22)

#4

Just to clarify, it was not my example, it was a sample that someone sent to me to post on the Integra web site. I have, unfortunately, lost the record of who it came from. That’s why the author name is listed as “unknown”.

As far as the cleanup; if you look for the KILL command in VBA that should allow you to clean up unwanted files. You issue the command with a path and file, and it will erase it.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

I have one small problem with this code. The Excel file does not do an autofit resulting in ######. How do I overcome this?

Thanks,
Dinesh.

Dinesh (BOB member since 2002-08-22)

#6

Look in the Excel object model for the proper function calls. You’ll want to select the entire sheet and then issue the autofit command. I don’t have the syntax in front of me, but I’ve seen it done.

This is the sort of thing that we had in mind when the “code” area was set up. If we continue to develop and enhance the macro, these suggestions should be recorded in the “code” area so that future readers can benefit. If you do get this working, please post your code back here so we can update the library.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#7

What I have in my Excel VBA book is…

range.AutoFit()

So it would look like…

Range("A1:A2").AutoFit

Eileen King :us: (BOB member since 2002-07-10)

#8

Where should I add the range function? Below is the code I am using:

[edited by Eileen to put into “Code”]

Set vbExcel = CreateObject("Excel.Application")
With vbExcel
  'Create a new workbook to import text files into
  If Dir(Path &amp; ExcelDoc &amp; ".xls") <> " " Then
    Kill Path &amp; ExcelDoc &amp; ".xls"
  End If
  .Workbooks.Add
  .ActiveWorkbook.SaveAs Path &amp; ExcelDoc &amp; ".xls"
  'Open source workbook, save as an Excel file, and import into destination Excel file.
  For i = 1 To Doc.Reports.Count
    Set Rep = Doc.Reports.Item(i)
    .Workbooks.Open Path &amp; Rep.Name &amp; ".txt"
    .ActiveWorkbook.SaveAs Path &amp; Rep.Name &amp; ".xls"
    'Copy worksheets to the destination Excel document
    .Workbooks(Rep.Name &amp; ".xls").Sheets(Rep.Name).Move _
    Before:=.Workbooks(ExcelDoc &amp; ".xls").Sheets("Sheet1")
    Kill Path &amp; Rep.Name &amp; ".txt"
    Kill Path &amp; Rep.Name &amp; ".xls"
  Next i
  'Save workbooks
  .ActiveWorkbook.Save
  'Close both workbooks
  .Workbooks.Close
  'Close Excel application object
  .Quit
End With

Thanks,
Dinesh.

Dinesh (BOB member since 2002-08-22)

#9

I would put it right before “Next i”…That way each time you bring over a worksheet, you’ll format it to fit! You will have to calculate the range for each worksheet first…

Eileen King :us: (BOB member since 2002-07-10)