Referencing query prompt date via vba

[DrSmyth]

I need to reference a query prompt in vba, the query prompt is called [end] and it's used as part of a file nameing section of code.

I currently hav mydate=date (mydate is date variable) but i want something along the lines of mydate = end (tried this but it doesn't work, any suggestions?

 

[Sridharan]

To reference a Prompt in VBA :-

Dim Doc as Document
Set Doc = ActiveDocument
Msgbox Doc.Variables(&quot;<Prompt Name>&quot;).Value

Hope that gives you an idea of prompt handling in VBA.

Coming to the second thing that you've mentioned. I dunno where you are attempting this. Is it at the QP or in VBA???
It should work. Basically I think you are looking at prompting the date from the User.

Sri

 

[DrSmyth]

Hi, i'm using this in vba and the user has already entered a prompt. It's basically used for naming a document.

I'm guessing i could use mydate = Variables(&quot;<end>&quot.value

I'll give it a go

 

[DrSmyth]

Ok, that doesn't work, just to clarify matters, this is the code i'm currently using

Sub export()
Dim doc As Document
Dim rep As Report

Dim mydate As Date
mydate = Date

Set doc = Application.Documents.Item(1)
Set rep = doc.Reports.Item(1)

rep.ExportAsText (&quot;F:\MI\Weekly\Unallocated_&quot; + Format(mydate, &quot;dd-mm-yyyy&quot + &quot;.xls&quot

End Sub

and i want mydate to be the user prompt date as opposed to current date..

 

[Sridharan]

What is the code that you have used??? Lemme take you thru an example. Suppose the Prompt Name is say [end]. Then you gotta do something like this...

Dim MyDate as Date
Set MyDate = ActiveDocument.Variables("[end]").Value
' The above will get the prompt value and store it in MyDate

Now you can use that MyDate to store the FileName with the User Entered Date Value.

Good Luck
Sri