VBA Script Help

sdk development
1

I have a situation where I’m filling in the prompts for the report. I have 2 dataproviders, 1 is the report itself and 2 is pulling the dates to use for the prompts of dataprovider 1. If I refresh the 2nd dataprovider BO asks me to fill in the prompts manually even though I’m supplying them. If I comment out the refresh of the 2nd dataprovider then it will run perfect…what’s the deal? Even if I substitute var1 and var2 for “04/01/2000” and “04/10/2000” it will still ask me to fill in the values if I try to .refresh the dataprovider and it doesn’t if I comment it out.

In a nutshell this is what I have :

ActiveDocument.DataProviders.Item(2).Refresh

var1 = data retrieved from dp2
var2 = data retrieved from dp2

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1 Application.Variables.Item(“2) Please enter an End Ship Date”).Value = var2 ActiveDocument.DataProviders.Item(1).Refresh

Listserv Archives (BOB member since 2002-06-25)

2

Ian,

What is your 2nd data provider? Does it pull the dates from database?

Do you have @variable() or @prompt() in 2nd data provider? From what I have understood, it shouldn’t have any. It should only refer to the objects, which pull data from database.

Vasan

Ian.Humphries@POWERQUEST.COM on 04/17/2000 01:14:40 PM

I have a situation where I’m filling in the prompts for the report. I have 2 dataproviders, 1 is the report itself and 2 is pulling the dates to use for the prompts of dataprovider 1. If I refresh the 2nd dataprovider BO asks me to fill in the prompts manually even though I’m supplying them. If I comment out the refresh of the 2nd dataprovider then it will run perfect…what’s the deal? Even if I substitute var1 and var2 for “04/01/2000” and “04/10/2000” it will still ask me to fill in the values if I try to .refresh the dataprovider and it doesn’t if I comment it out.

In a nutshell this is what I have :

ActiveDocument.DataProviders.Item(2).Refresh

var1 = data retrieved from dp2
var2 = data retrieved from dp2

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1 Application.Variables.Item(“2) Please enter an End Ship Date”).Value = var2 ActiveDocument.DataProviders.Item(1).Refresh

******************************************************************************* Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Ernst & Young LLP

Listserv Archives (BOB member since 2002-06-25)

3

Both are SQL and the second dataprovider is simply pulling the first day of last week and the last day of the previous week.

Ian
,

What is your 2nd data provider? Does it pull the dates from database?

Do you have @variable() or @prompt() in 2nd data provider? From what I have understood, it shouldn’t have any. It should only refer to the objects, which pull data from database.

Vasan

I have a situation where I’m filling in the prompts for the report. I have 2 dataproviders, 1 is the report itself and 2 is pulling the dates to use for the prompts of dataprovider 1. If I refresh the 2nd dataprovider BO asks me to fill in the prompts manually even though I’m supplying them. If I comment out the refresh of the 2nd dataprovider then it will run perfect…what’s the deal? Even if I substitute var1 and var2 for “04/01/2000” and “04/10/2000” it will still ask me to fill in the values if I try to .refresh the dataprovider and it doesn’t if I comment it out.

In a nutshell this is what I have :

ActiveDocument.DataProviders.Item(2).Refresh

var1 = data retrieved from dp2
var2 = data retrieved from dp2

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1 Application.Variables.Item(“2) Please enter an End Ship Date”).Value = var2 ActiveDocument.DataProviders.Item(1).Refresh

Listserv Archives (BOB member since 2002-06-25)

4

SQL of 2nd data provider shouldn’t have any @variable() or @prompt().

Can you send me the SQL of 2nd data provider…

Vasan

Ian.Humphries@POWERQUEST.COM on 04/17/2000 02:47:30 PM

Both are SQL and the second dataprovider is simply pulling the first day of last week and the last day of the previous week.

Ian
,

What is your 2nd data provider? Does it pull the dates from database?

Do you have @variable() or @prompt() in 2nd data provider? From what I have understood, it shouldn’t have any. It should only refer to the objects, which pull data from database.

Vasan

I have a situation where I’m filling in the prompts for the report. I have 2 dataproviders, 1 is the report itself and 2 is pulling the dates to use for the prompts of dataprovider 1. If I refresh the 2nd dataprovider BO asks me to fill in the prompts manually even though I’m supplying them. If I comment out the refresh of the 2nd dataprovider then it will run perfect…what’s the deal? Even if I substitute var1 and var2 for “04/01/2000” and “04/10/2000” it will still ask me to fill in the values if I try to .refresh the dataprovider and it doesn’t if I comment it out.

In a nutshell this is what I have :

ActiveDocument.DataProviders.Item(2).Refresh

var1 = data retrieved from dp2
var2 = data retrieved from dp2

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1 Application.Variables.Item(“2) Please enter an End Ship Date”).Value = var2 ActiveDocument.DataProviders.Item(1).Refresh

******************************************************************************* Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Ernst & Young LLP

Listserv Archives (BOB member since 2002-06-25)

5

Here is the SQL of the second dataprovider…

select
to_date(trunc(sysdate-7) ,‘mm/dd/yyyy hh24:mi:ss’)-1 StartDate, to_date(trunc(sysdate) ,‘mm/dd/yyyy hh24:mi:ss’)-1/63000 EndDate from dual

I have a situation where I’m filling in the prompts for the report. I have 2 dataproviders, 1 is the report itself and 2 is pulling the dates to use for the prompts of dataprovider 1. If I refresh the 2nd dataprovider BO asks me to fill in the prompts manually even though I’m supplying them. If I comment out the refresh of the 2nd dataprovider then it will run perfect…what’s the deal? Even if I substitute var1 and var2 for “04/01/2000” and “04/10/2000” it will still ask me to fill in the values if I try to .refresh the dataprovider and it doesn’t if I comment it out.

In a nutshell this is what I have :

ActiveDocument.DataProviders.Item(2).Refresh

var1 = StartDate(from SQL of dataprovider 2 above) var2 = EndDate(from SQL of dataprovider 2 above)

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1 Application.Variables.Item(“2) Please enter an End Ship Date”).Value = var2 ActiveDocument.DataProviders.Item(1).Refresh

Listserv Archives (BOB member since 2002-06-25)

6

That’s funny. When you refresh 2nd data provider, it shouldn’t ask you for the dates. Only when you refresh 1st data provider it should ask.

Did you try to refresh only 2nd data provider, directly from BO?

Vasan

Ian.Humphries@POWERQUEST.COM on 04/17/2000 03:23:29 PM

Here is the SQL of the second dataprovider…

select
to_date(trunc(sysdate-7) ,‘mm/dd/yyyy hh24:mi:ss’)-1 StartDate, to_date(trunc(sysdate) ,‘mm/dd/yyyy hh24:mi:ss’)-1/63000 EndDate from dual

******************************************************************************* Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Ernst & Young LLP

Listserv Archives (BOB member since 2002-06-25)

7

Ian,

Instead of Application Variables, try with ActiveDocument Variables like this :

ActiveDocument.DataProviders.Item(2).Refresh

var1 = data retrieved from dp2
var2 = data retrieved from dp2

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

ActiveDocument.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1
ActiveDocument.Variables.Item(“2) Please enter an End Ship Date”).Value = var2
ActiveDocument.DataProviders.Item(1).Refresh

Hope this helps.
JP

Listserv Archives (BOB member since 2002-06-25)

8

I don’t know where you put this code, but this might help: If you perform a refresh of a dataprovider in the “before refresh” event of a document all dataproviders are refreshed! Don’t ask me why, according to BO support it is that way and it will remain so.

Geert Janssens.

In a nutshell this is what I have :

ActiveDocument.DataProviders.Item(2).Refresh

Listserv Archives (BOB member since 2002-06-25)

9

Hello,

I think I had the same problem, try using the following lines of code in the right places:

application.interactive = false

and

application.interactive = true

Bye,

Robert Duindam
Fortis

DISCLAIMER******** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan en gebruik door anderen is niet toegestaan. Fortis sluit iedere aansprakelijkheid uit die voortvloeit uit electronische verzending.

This e-mail is intended exclusively for the addressee(s), and may not be passed on to, or made available for use by any person other than the addressee(s).
Fortis rules out any and every liability resulting from any electronic transmission.

Listserv Archives (BOB member since 2002-06-25)

10

This report will never be interactive…ie it is only for DAS scheduling only. Where would I use em and why? heh Here’s the scriptin in its entirety.

Sub main()
Dim vars As Variables
Dim I, J As Integer
Dim var1, var2 As String
Dim thisdoc As Document
Dim docvars As DocumentVariable

Set thisdoc = Application.ActiveDocument

ActiveDocument.DataProviders.Item(2).Refresh

For I = 1 To thisdoc.DocumentVariables.Count
If UCase(thisdoc.DocumentVariables.Item(I).Name) Like “START*” Then
For J = 1 To 2
var1 = (thisdoc.DocumentVariables.Item(I).Values(BoAllValues)(1)) Next J
End If
Next I

For I = 1 To thisdoc.DocumentVariables.Count
If UCase(thisdoc.DocumentVariables.Item(I).Name) Like “END*” Then
For J = 1 To 2
var2 = (thisdoc.DocumentVariables.Item(I).Values(BoAllValues)(1)) Next J
End If
Next I

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship Date”).Delete Application.Variables.Item(“2) Please enter an End Ship Date”).Delete

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1 Application.Variables.Item(“2) Please enter an End Ship Date”).Value = var2

ActiveDocument.Refresh

End Sub

robert.duindam@NL.FORTIS.COM 04/18/00 02:57AM >>>
Hello,

I think I had the same problem, try using the following lines of code in the right places:

application.interactive = false

and

application.interactive = true

Bye,

Robert Duindam
Fortis

DISCLAIMER******** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan en gebruik door anderen is niet toegestaan. Fortis sluit iedere aansprakelijkheid uit die voortvloeit uit electronische verzending.

This e-mail is intended exclusively for the addressee(s), and may not be passed on to, or made available for use by any person other than the addressee(s).
Fortis rules out any and every liability resulting from any electronic transmission.

Listserv Archives (BOB member since 2002-06-25)

11

My bad, we are using 5.02, BCA and VBA…we just upgraded from 4.x so I’m still catching up on my terms :slight_smile:

robert.duindam@NL.FORTIS.COM 04/18/00 09:29AM >>>
Hi Ian,

I’m sorry but I thought you were using BO 5.x, BCA and VBA. So my suggestion is not of any use to you!

Robert Duindam

Listserv Archives (BOB member since 2002-06-25)

12

Hi Ian,

I’m sorry but I thought you were using BO 5.x, BCA and VBA. So my suggestion is not of any use to you!

Robert Duindam

From: Ian Humphries[SMTP:Ian.Humphries@POWERQUEST.COM]
Sent: 18-4-2000 3:23 PM

This report will never be interactive…ie it is only for DAS scheduling only. Where would I use em and why? heh Here’s the scriptin in its entirety.

Sub main()
Dim vars As Variables
Dim I, J As Integer
Dim var1, var2 As String
Dim thisdoc As Document
Dim docvars As DocumentVariable

Set thisdoc = Application.ActiveDocument

ActiveDocument.DataProviders.Item(2).Refresh

For I = 1 To thisdoc.DocumentVariables.Count
If UCase(thisdoc.DocumentVariables.Item(I).Name) Like “START*” Then
For J = 1 To 2
var1 =
(thisdoc.DocumentVariables.Item(I).Values(BoAllValues)(1))
Next J
End If
Next I

For I = 1 To thisdoc.DocumentVariables.Count
If UCase(thisdoc.DocumentVariables.Item(I).Name) Like “END*” Then
For J = 1 To 2
var2 =
(thisdoc.DocumentVariables.Item(I).Values(BoAllValues)(1))
Next J
End If
Next I

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship Date”).Delete Application.Variables.Item(“2) Please enter an End Ship Date”).Delete

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value = var1
Application.Variables.Item(“2) Please enter an End Ship Date”).Value = var2

ActiveDocument.Refresh

End Sub

robert.duindam@NL.FORTIS.COM 04/18/00 02:57AM >>>
Hello,

I think I had the same problem, try using the following lines of code in the
right places:

application.interactive = false

and

application.interactive = true

Bye,

Robert Duindam
Fortis

DISCLAIMER******** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan en gebruik door anderen is niet toegestaan. Fortis sluit iedere aansprakelijkheid uit die voortvloeit uit electronische verzending.

This e-mail is intended exclusively for the addressee(s), and may not be passed on to, or made available for use by any person other than the addressee(s).
Fortis rules out any and every liability resulting from any electronic transmission.

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)

13

Ok,

then try using:
put the interactive code around the last activedocument.refresh (ie your last line of code):

application.interactive = false
activedocument.refresh
application.interactive = true

Again, I’m not sure we have the same problem. It prevents de prompt dialogbox of coming up!

Gr.
Robert Duindam

From: Duindam, R. (Robert)[SMTP:robert.duindam@NL.FORTIS.COM]
Sent: 18-4-2000 4:29 PM

Hi Ian,

I’m sorry but I thought you were using BO 5.x, BCA and VBA. So my suggestion
is not of any use to you!

Robert Duindam

From: Ian Humphries[SMTP:Ian.Humphries@POWERQUEST.COM]
Sent: 18-4-2000 3:23 PM

This report will never be interactive…ie it is only for DAS scheduling only. Where would I use em and why? heh Here’s the scriptin in its entirety.

Sub main()
Dim vars As Variables
Dim I, J As Integer
Dim var1, var2 As String
Dim thisdoc As Document
Dim docvars As DocumentVariable

Set thisdoc = Application.ActiveDocument

ActiveDocument.DataProviders.Item(2).Refresh

For I = 1 To thisdoc.DocumentVariables.Count
If UCase(thisdoc.DocumentVariables.Item(I).Name) Like “START*” Then
For J = 1 To 2
var1 =
(thisdoc.DocumentVariables.Item(I).Values(BoAllValues)(1))
Next J
End If
Next I

For I = 1 To thisdoc.DocumentVariables.Count
If UCase(thisdoc.DocumentVariables.Item(I).Name) Like “END*” Then
For J = 1 To 2
var2 =
(thisdoc.DocumentVariables.Item(I).Values(BoAllValues)(1))
Next J
End If
Next I

var1 = Format(var1, “mm/dd/yyyy HH:mm:ss”) var2 = Format(var2, “mm/dd/yyyy HH:mm:ss”)

Application.Variables.Item(“1) Please enter a Start Ship
Date”).Delete
Application.Variables.Item(“2) Please enter an End Ship Date”).Delete

Application.Variables.Item(“1) Please enter a Start Ship Date”).Value
=
var1
Application.Variables.Item(“2) Please enter an End Ship Date”).Value
=
var2

ActiveDocument.Refresh

End Sub

robert.duindam@NL.FORTIS.COM 04/18/00 02:57AM >>>
Hello,

I think I had the same problem, try using the following lines of code in the
right places:

application.interactive = false

and

application.interactive = true

Bye,

Robert Duindam
Fortis

DISCLAIMER******** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan en gebruik door anderen is niet toegestaan. Fortis sluit iedere aansprakelijkheid uit die voortvloeit uit electronische verzending.

This e-mail is intended exclusively for the addressee(s), and may not be passed on to, or made available for use by any person other than the addressee(s).
Fortis rules out any and every liability resulting from any electronic transmission.

Listserv Archives (BOB member since 2002-06-25)