Send to BCA with 1 prompt and save as excel - VBA code help

system · June 4, 2008, 11:46am

Hello,

I am struggling to get my report into the BCA, it has one prompt on Route (eg. ‘New York’) and I need it to be saved in excel for every route eg. New York.xls

I have been searching the forum and managed to find various bits of VBA code and have managed to get it so it goes into the BCA but it fails with the error

1010 The Subroutine is not defined.

Here is my code:

Sub SendToBCAServer()
Dim Vars As Variables
Dim BCA As DocAgentOption
Set doc = ThisDocument
Set Vars = Application.ActiveDocument.Variables
Set BCA = ThisDocument.DocAgentOption
BCA.Server = “vhbca1”
BCA.ScheduleMode = boOnce
BCA.Title = “NRDtest”
BCA.DistributionFolder = “C:\temp”
BCA.StartDate = Now
BCA.EndDate = Now + 365
BCA.Send
Debug.Print "start: " & Now()
ScriptStartTime = Now()

FilePath = Application.ActiveDocument.Path & ""
ParmFile = FreeFile
Open FilePath & “Parameters.txt” For Input As #ParmFile
Do While Not EOF(ParmFile)
BCA.ResetToDefault
Set ReportNameInfo = Application.ActiveDocument.Variables.Add(“reportnamevar”)
Line Input #ParmFile, ReportNameValue
Line Input #ParmFile, ReportDescValue
Line Input #ParmFile, ReportFrequencyValue
Line Input #ParmFile, ReportScheduleHourValue
Line Input #ParmFile, ReportPrompt1Value
Line Input #ParmFile, ReportSeparatorDummy
Debug.Print "Name " & ReportNameValue
Debug.Print "Desc " & ReportDescValue
Debug.Print "Frequency " & ReportFrequencyValue
Debug.Print "Schedule " & ReportScheduleHourValue
Debug.Print "Wkgrp " & ReportPrompt1Value
ReportNameInfo.Value = ReportNameValue

Vars("Route?").Value = ReportPrompt1Value

BCA.Title = ReportDescValue
 doc.Refresh
 doc.SaveAs ("C:\" &amp; doc.Name &amp; ".xls")
 doc.Close

Loop
Debug.Print "time delta: " & DateDiff(“s”, Now(), ScriptStartTime)
Application.Interactive = True
End Sub

Here are the values in my parameter file:

“NRD.rep”
“New Route Download W”
“New York”
1
“NRD.rep”
“New Route Download W”
“Washington”
1
“NRD.rep”
“New Route Download A”
“Antigua”
1

I have never done any VBA code before so I am really struggling, not sure if I even have the format of the parameter file right

Thanks in advance for any help!!!

cheers
Claire

Claire Everitt (BOB member since 2002-09-03)

system · June 4, 2008, 12:39pm

Can you rephrase your actual requirement.
Because its difficult to understand what you are after looking at that code you got from somewhere.
If you are trying to loop through an object in the query and create XLS files then are other codes on BOB, search the SDK forum with keywords “forcecompute”

.

haider (BOB member since 2005-07-18)

system · June 4, 2008, 12:57pm

Hi Haider,

Thanks for the reply.

My requirement is to send a report to BCA that has prompt on Route. There are 20 Routes that the report needs to run for.

I need a copy of this report in excel for every Route. So I will get 20 excel files as the output.

eg. “new york.xls”, “washington.xls”, “antigua.xls”

I just want to have 1 report to send to BCA (using a parameter file) not 20 reports (one per route).

Hope that makes sense!

Thanks
Claire

Claire Everitt (BOB member since 2002-09-03)

system · June 5, 2008, 7:19pm

We need your current version of BO

dirmiger (BOB member since 2002-08-28)

system · June 5, 2008, 8:21pm

Hi,

Sorry, i’m sending it from Full Client 6.5.1

Thanks
Claire

Claire Everitt (BOB member since 2002-09-03)

system · June 8, 2008, 6:02am

Then I would suggest to remove prompt in the report and just loop through the route object to create XLS files
Read this thread

.

haider (BOB member since 2005-07-18)

system · June 10, 2008, 12:26pm

Hi Haider,

Thanks for your help it is greatly appreciated.

I have added this code into my report but it isnt working as it restricts to the first route only (I am testing it with 2 routes and the count shows 2).
It produces 2 excel files but the contents are the same in each file (the data for the 1st route).

Please help!!

Thanks
Claire

Sub ddf()
Dim oobject As Column
Set oobject = ThisDocument.DataProviders(“TRD”).Columns(1)
For i = 1 To oobject.Count
ThisDocument.Reports(1).AddComplexFilter “Route(TRD)”, “=<Route(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(1).ForceCompute
ThisDocument.SaveAs “C:” & oobject.Item(i) & “.xls”
Next
End Sub

Claire Everitt (BOB member since 2002-09-03)

system · June 10, 2008, 12:50pm

Ensure few things

the column “Route(TRD)” referred to in the code as (1) is actually placed as the first object in the DP.
Check the filters after the code has run, does it show the second value in the object or first

Also run it in debug mode (ALT+F’8’)

.

haider (BOB member since 2005-07-18)

system · June 10, 2008, 1:02pm

Hi Haider,

Yes, the column “Route(TRD)” is the first object in the DP (it is the only object in the DP).

After the code has run was showing the SECOND value in the filters. It is now creating the filter but not applying any restriction at all.

Just tried it in debug mode and when I hover over this line:

Set oobject = ThisDocument.DataProviders(“TRD”).Columns(1)

It says “object variable or with block variable not set” and “oobject=nothing”

Just tried again and realised I forgot to say that I have 16 tabs (seperate reports) in my report. Do I need to repeat the code for every tab? As it seems only the first tab is working?

Thanks
Claire

UPDATE: I think I have it working now. I repeated the code for all 16 tabs and i’m just testing it now. Thank you Haider - I wouldn’t of been able to do it without your help!

Here is the new code:

Sub test()
Dim oobject As Column
Set oobject = ThisDocument.DataProviders(“TRD”).Columns(1)
For i = 1 To oobject.Count

ThisDocument.Reports(1).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(1).ForceCompute
ThisDocument.Reports(2).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(2).ForceCompute
ThisDocument.Reports(3).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(3).ForceCompute
ThisDocument.Reports(4).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(4).ForceCompute
ThisDocument.Reports(5).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(5).ForceCompute
ThisDocument.Reports(6).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(6).ForceCompute
ThisDocument.Reports(7).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(7).ForceCompute
ThisDocument.Reports(8).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(8).ForceCompute
ThisDocument.Reports(9).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(9).ForceCompute
ThisDocument.Reports(10).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(10).ForceCompute
ThisDocument.Reports(11).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(11).ForceCompute
ThisDocument.Reports(12).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(12).ForceCompute
ThisDocument.Reports(13).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(13).ForceCompute
ThisDocument.Reports(14).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(14).ForceCompute
ThisDocument.Reports(15).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(15).ForceCompute
ThisDocument.Reports(16).AddComplexFilter “True Route Destination(TRD)”, “=<True Route Destination(TRD)>=”"" & oobject.Item(i) & “”""
ThisDocument.Reports(16).ForceCompute

ThisDocument.SaveAs “C:\NRDFILES” & oobject.Item(i) & “.xls”
Next
End Sub

Claire Everitt (BOB member since 2002-09-03)

system · June 11, 2008, 11:20am

Ok I have it working now.

Is there any way to send it to BCA to run? I can get it to run via BCA but it doesnt produce the files.

Thanks
Claire

Claire Everitt (BOB member since 2002-09-03)

system · June 11, 2008, 11:38am

To schedule it in BCA, first open the report interactively in full client on the BCA server and run it once. There may be references missing.
And you are using custom macros in the actions tab of BCA while scheduling it?

.

haider (BOB member since 2005-07-18)

system · June 11, 2008, 12:17pm

Nope forgot that bit added it in now and its all working fine.

Thanks so much for your help haider

Claire

Claire Everitt (BOB member since 2002-09-03)

system · June 11, 2008, 12:45pm

Glad to know its working

.

haider (BOB member since 2005-07-18)