export reports in one xls file : many problems

[system]

hye,

i use a small modified version of the code to export multiple BO reports in one xls file.

here is the code :

' ---------------------------------------------------------------
' exportation au format xls
' ---------------------------------------------------------------
' principe : exportation des différents rapports au format texte,
'            puis importation dans des feuilles excel.
' ---------------------------------------------------------------
Sub export_Excel(lparam As String)

On Error GoTo error_handler

Dim Doc As Document
Dim Rep As Report
Dim i As Integer
Dim ExcelDoc As String ' nom du fichier excel cible
Dim Path As String ' lien pour le fichier excel cible

ExcelDoc = ActiveDocument.Name & lparam ' nom du ficher excel
Path = "P:\test\"  ' lien pour stocker le fichier excel


' enregistre les rapports au format text
Set Doc = ActiveDocument
For i = 1 To 2
  Set Rep = Doc.Reports.Item(i)
If Dir(Path &amp; Rep.Name &amp; ".txt") <> " " Then
    Kill Path &amp; Rep.Name &amp; ".txt"
  End If
  Rep.ExportAsText (Path &amp; Rep.Name &amp; ".txt")
Next i

Set vbExcel = CreateObject("Excel.Application")
With vbExcel
' création d'un fichier excel, qui contiendra les ficher au format txt
  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"
' ouvre les sources, enregistre au formazt excel, et immport dans la bonne destination
' For i = 1 To Doc.Reports.Count
For i = 1 To 2
  Set Rep = Doc.Reports.Item(i)
  .Workbooks.Open Path &amp; Rep.Name &amp; ".txt"
  If Dir(Path &amp; Rep.Name &amp; ".xls") <> " " Then
    Kill Path &amp; Rep.Name &amp; ".xls"
  End If
  .ActiveWorkbook.SaveAs Path &amp; Rep.Name &amp; ".xls"
  ' copie des feuilles dans le fichier excel
    .Workbooks(Rep.Name &amp; ".xls").Sheets(Rep.Name).Move _
      Before:=.Workbooks(ExcelDoc &amp; ".xls").Sheets("Sheet1")
Next i
' ferme tout les workbooks
  .ActiveWorkbook.Save
  Workbooks.Close
' ferme l'application
  .Quit
End With
  ' libère la mémoire
  Set vbExcel = Nothing

Exit Sub

error_handler:
If Err.Number = 53 Then Resume Next
  MsgBox Err.Number &amp; " - " &amp; Err.Description
  Workbooks.Close
  vbExcel.Quit
  Set vbExcel = Nothing
    Set OlkApp = Nothing

End Sub

but i have somme problem.

i’ve got an error : 70 - permissio denied
needed object…

any idea.

i just want to export in a repository, an xls made with all report of a BO document.

thanks for every.

have a nice day.

---

jbachet (BOB member since 2004-04-16)

[system]

The code is fine except for one small oversight. Please see the corrected code below. There was no dot before Workbooks.Close!!

' --------------------------------------------------------------- 
' exportation au format xls 
' --------------------------------------------------------------- 
' principe : exportation des différents rapports au format texte, 
'            puis importation dans des feuilles excel. 
' --------------------------------------------------------------- 
Sub export_Excel(lparam As String) 

On Error GoTo error_handler 

Dim Doc As Document 
Dim Rep As Report 
Dim i As Integer 
Dim ExcelDoc As String ' nom du fichier excel cible 
Dim Path As String ' lien pour le fichier excel cible 

ExcelDoc = ActiveDocument.Name & lparam ' nom du ficher excel 
Path = "P:\test\"  ' lien pour stocker le fichier excel 


' enregistre les rapports au format text 
Set Doc = ActiveDocument 
For i = 1 To 2 
  Set Rep = Doc.Reports.Item(i) 
If Dir(Path &amp; Rep.Name &amp; ".txt") <> " " Then 
    Kill Path &amp; Rep.Name &amp; ".txt" 
  End If 
  Rep.ExportAsText (Path &amp; Rep.Name &amp; ".txt") 
Next i 

Set vbExcel = CreateObject("Excel.Application") 
With vbExcel 
' création d'un fichier excel, qui contiendra les ficher au format txt 
  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" 
' ouvre les sources, enregistre au formazt excel, et immport dans la bonne destination 
' For i = 1 To Doc.Reports.Count 
For i = 1 To 2 
  Set Rep = Doc.Reports.Item(i) 
  .Workbooks.Open Path &amp; Rep.Name &amp; ".txt" 
  If Dir(Path &amp; Rep.Name &amp; ".xls") <> " " Then 
    Kill Path &amp; Rep.Name &amp; ".xls" 
  End If 
  .ActiveWorkbook.SaveAs Path &amp; Rep.Name &amp; ".xls" 
  ' copie des feuilles dans le fichier excel 
    .Workbooks(Rep.Name &amp; ".xls").Sheets(Rep.Name).Move _ 
      Before:=.Workbooks(ExcelDoc &amp; ".xls").Sheets("Sheet1") 
Next i 
' ferme tout les workbooks 
  .ActiveWorkbook.Save 
  .Workbooks.Close 
' ferme l'application 
  .Quit 
End With 
  ' libère la mémoire 
  Set vbExcel = Nothing 

Exit Sub 

error_handler: 
If Err.Number = 53 Then Resume Next 
  MsgBox Err.Number &amp; " - " &amp; Err.Description 
  Workbooks.Close 
  vbExcel.Quit 
  Set vbExcel = Nothing 
    Set OlkApp = Nothing 

End Sub 

---

avaksi (BOB member since 2002-08-22)

[system]

in fact, this code is note good for what i want to do, because i don’t want to loose the formated (color…) tabs and graph…

also, it don’t works with graph reports…

so, i have an idea :

export each report of the document into a xls file.

using VBA to create a Excel file.
copy all the xls files into the new one.
one sheet per “old” file

but thank you for having corrected my code.

---

jbachet (BOB member since 2004-04-16)

[system]

I thought your code above was doing exactly that!!

Anyways, if you want to keep the charts and colors then you should look at converting html to excel. We had a discussion some time back on this forum and I had posted some code there that might be helpful.

---

avaksi (BOB member since 2002-08-22)

[system]

exactly… i would like to keep color and format…

but i can’t find the topic you teld me.

i’ve made a search with your name, but no way to find out. !!

where is ti ???

---

jbachet (BOB member since 2004-04-16)

[system]

Check here. Go down the page and you will see some code that converts an .rep file to HTML and then converts all the HTML files to excel. I think the graphs come out as pictures.

Please post any comments or questions in that forum so that all posts are in one place.

---

avaksi (BOB member since 2002-08-22)

[system]

There is a way to not loose the format, but it not very practical. Yuo can take control of BO at the application level and with code do select all/ copy all then go to excel paste from clipboard with pastespecial. I did it but the performance was horrible.

---

dvraggs (BOB member since 2003-10-13)

[system]

ok, thank you very much…

buch for the moment, it does’nt really mater, cause i’ve got some more important problems…

---

jbachet (BOB member since 2004-04-16)