HI
I am new to bo site. i have a problem. i have a report say with 50 pages. Now my problem is that i want each page of the report to be exported as different excel file. means that 50 pages = 50 excel files. ( page1 of report as excel file1, page2 of report as excel file2 and so no.)
thanks
rajx72 (BOB member since 2006-08-02)
welcome to B 
B
kool 
(BOB member since 2005-04-15)
If you are using BO 5.1 then https://bobj-board.org/t/20393 will help, have a look.
shyamdev 
(BOB member since 2005-08-05)
Hi all
Thanks for the quick replies.
I am using BO 6.5. and I have a report with 50 pages and each page has to be saves as different excel file. means 50 excel files copied on 50 folders.
can we do it in Bo
thanks
rajx72 (BOB member since 2006-08-02)
You will definitley need some BO SDK solution (may be VBA code), do a search in that section there.
May be this thread needs to be moved to that section (SDK) by MOD
shyamdev (BOB member since 2005-08-05)
Done … and duplicate post in the SDK forum removed.
Dwayne Hoffpauir 
(BOB member since 2002-09-19)
HI all
Is there a way to do this in BO. waiting for your responses.
thanks in advance
rajx72 (BOB member since 2006-08-02)
Raj:
I posted the code here:Duplicate here
Found the same topic again here…Again pasting the same code in this thread, it will be easier for Moderator to lock the Duplicate one and not this, we are continuing in this:
you can modify it accordingly:
Sub Refresh_for_Each_Employee()
Dim i as integer
Dim j as string
For i = 1 to 20 step 1
j = 'you must assign value of employee name here........
ActiveDocument.DocumentVariables.Item("Enter Employee Name").Value = j
ActiveDocument.refresh
ActiveDocument.ExportAsExcel "C:\foldername\EmployeeName"&j
Next i
End Sub This topic is discussed previously, you’ll find similar routines to achieve your objective… Search in this forum using ‘report refresh’…or similar terms.
BO_Chief (BOB member since 2004-06-06)
hi chief
let me tell me my problem i have make a report and then export it to excel.
there are 2 ways i can make a report.
one is i just make one report with all the data say emp data with each page showing different employee detail. Now if i have 20 rec. in database then this will give me 20 pages. Now my problem is that if i save this file as excel it will come in 1 excel file. but i need each page as different excel file.
how can i make each page of report as different excel file. 20 record in database is example. i dont know how many records are there.
Second option is I promt the user to select empname. if i use this the user has to refresh the report say 20 times and save it as excel. How can i automate this process. Here also i dont know how many records are there in database.
thanks
rajx72 (BOB member since 2006-08-02)
@Raj:
I have gone through your post earlier, I know what you are explaining me again.!!
The code which I gave above will work for 20 employees.
If you want to run this for all the employees in your table, you can do that too. you can’t find an exact code for your requirement… you have to modify it…
Try to understand the code, use it for your purpose…to automate it.
Search for other VBA routines as well… in this forum… to get an idea… of VBA routines.
I have learned VBA through this forum, thanks to all board members for sharing their scripts, you can learn too !! But its not an overnight job!!
So search search search…try to understand OBJECT MODEL…
Long live BOB. 
BO_Chief (BOB member since 2004-06-06)
hi
thanks for reply. i just wanted to know as we can loop through database in VB using Ado and recordset can we do it in Bo also.
I will work on it.
thanks
rajx72 (BOB member since 2006-08-02)
Yes you can, but you can create a DP (say DP1) which will give you all the employee names.
Now create another DP(say DP2), take all the employee names from DP1, loop through it, and then run DP2.
Ok…
I like that… thats the spirit.
BO_Chief (BOB member since 2004-06-06)
I hope you were able to resolve it … if you did, then paste the code here that might help other board members… with similar issues.
If you take tips/hints from this board, learn to share as well, help the board to grow.
BO_Chief (BOB member since 2004-06-06)
HI
I was busy in some other work at office. so could not work on it. as soon as i do it i will post my code on this post.
I have learned a lot from this site and fully agree that we should share our code so others can get help.
thanks
rajx72 (BOB member since 2006-08-02)
Heres the code which is working for me.
I have made 2 dp in the reports. 1st dp will show all the data and dp2 will
have only one column say LFC and will be used to pass the value to the prompt.
hope it is useful.
Sub ReadDataProv()
Dim DProv As DataProvider
Dim RowNum As Long
Dim lfcName As String
Dim spath As String
Set DProv = ThisDocument.DataProviders(“dp2”)
If DProv.Columns(1).Count > 0 Then 'be sure there is data
For RowNum = 1 To DProv.Columns(1).Count
lfcName = DProv.Columns(“Lfc”).Item(RowNum)
Application.Variables.Item(“Select Lfc1”).Value = lfcName
ActiveDocument.Refresh
spath = “C:” + lfcName + CStr(Year(Date)) + “.xls”
ActiveDocument.SaveAs spath
Next RowNum
End If
End Sub
thanks Bo-cheif for all the help.
this was one part of my problem. now i have to protect first 2 columns of each excel file through macro only.
I have started working on it. once i do it i will share the code. any help is most welcome.
rajx72 (BOB member since 2006-08-02)
there is one thingh still left. I have to copy these excel files to different folders.
means if there are 10 excel files i have to copy each file based on lfc name to 10 lfc name folders.
thanks
rajx72 (BOB member since 2006-08-02)
If you really want to do the export the you can use this code to filter your data on each value in turn which avoids pratting about with unnecessary DPs
Sub DataExportByCountry()
'Dim myvar As DocumentVariable
'Dim myvar2 As DocumentVariable
'Dim reps As busobj.Reports
'Dim thereport As busobj.Report
Dim str As String
Dim OutNum As Integer
Dim strCountryList As String
SaveName = ActiveDocument.Title
SavePath = ActiveDocument.Subject
strCountryList = SavePath & “CountriesExported.txt”
Set reps = ActiveDocument.Reports
Set thereport = busobj.ActiveReport
For I = 1 To ActiveDocument.DocumentVariables.Count
Set myvar = ActiveDocument.DocumentVariables.Item(I)
If myvar.Name = "Country Iso3 C" Then
myvalues = myvar.Values(boUniqueValues)
For Each myval In myvalues
For X = 1 To ActiveDocument.DocumentVariables.Count
Set myvar2 = ActiveDocument.DocumentVariables.Item(X)
If myvar2.Name = "Billed Year/Month(Sales CM)" Then
myvalues2 = myvar2.Values(boUniqueValues)
For Each myval2 In myvalues2
'Check if a folder for this Global Name - Trim already exists
If Dir(SavePath & Trim(myval), vbDirectory) = "" Then
'if it doesn't then we create one
MkDir (SavePath & "\" & Trim(myval))
End If
'Check if a subfolder for this report type exists
If Dir(SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title, vbDirectory) = "" Then
'if it doesn't then we create one
MkDir (SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title)
End If
'Check if a subfolder for this reporting month
If Dir(SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2, vbDirectory) = "" Then
'if it doesn't then we create one
MkDir (SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2)
End If
'Check if a subfolder for this reporting output (excel or PDF)
If Dir(SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2 & "\" & "Excel", vbDirectory) = "" Then
'if it doesn't then we create one
MkDir (SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2 & "\" & "Excel")
End If
'Check if a subfolder for this reporting output (excel or PDF)
If Dir(SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2 & "\" & "PDF", vbDirectory) = "" Then
'if it doesn't then we create one
MkDir (SavePath & "\" & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2 & "\" & "PDF")
End If
'start a loop to cycle through all report tabs
For z = 1 To reps.Count
Set thereport = reps.Item(CInt(z))
str = "=<Country Iso3 C> = """ & myval & """"
thereport.AddComplexFilter "Country Iso3 C", str
thereport.ForceCompute
thereport.ExportAsText SavePath & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2 & "\" & "Excel" & "\" & thereport.Name & ".xls"
Set thereport = Nothing
Next
ActiveDocument.ExportAsPDF SavePath & Trim(myval) & "\" & ActiveDocument.Title & "\" & myval2 & "\" & "PDF" & "\" & ActiveDocument.Author
OutNum = FreeFile
Open strCountryList For Append As OutNum
Print #OutNum, myval; Chr(9); Date; Time
Close #OutNum
Next
End If
Next
Next
End If
Next
End Sub
Just drop out any sections you don’t need. It really is very simple code.
DB
nwdb 
(BOB member since 2005-10-26)
Using the code BO users have provided in this post, I have created BO report in DesktopIntelligence. I am using BOXir2. When I run the macro on the Deski, my report works perfectly fine, and I have individual excel file for each sections. It works fine.
The problem is after I export it to the CMS. I have to schedule this report to run everyday and save these excel files on a Unix server. When I schedule the report, it gives me this error:
A variable prevented the data provider Main Report from being refreshed. (DMA0008) Even when I select the destination has default enterprise, it gives me this error. In the VBA code, I had the excel reports saved in a directory called ‘Events’ under C: drive. Since there is no C: drive in unix server, I thought it was giving me error because of that.
So I changed the path in the VBA code to point it to a directory on the unix server. I still get the same error.
I have enable and configured the destination as ‘Unmanaged Disk’ from the server properties also, and when I select the destination as ‘unmanaged disk’, I get the same error.
What am I doing wrong here? Any idea? I can post my code here if needed.
Thanks.
bits06 (BOB member since 2006-04-21)