Dyanmic location for transfer text

[shanmugamgsn]

Hi,

I have used Transfer text cmd to export query to text in MS access. This works fine.

path= "D:/test/"
DoCmd.TransferText acExportFixed,"Query1",path,False

But i need to change the path dyanmically as my wish when i run this query.
Like i may save my txt in desktop or C: or D:.
I dont want to hard code path as above.

Is there any way to achieve this?
If i get the dyanmic location stored in path variable i can achieve it.
But i dont know how to achieve this.

Actually i was confused

Docmd.OutputTo acOutputQuery,query1


Above query prompts me for selecting location.
But transferText doesn't? why?



As this application is stored on server. If i give a static path, its exporting in Server D:/test/
But i need this to be stored in my local. This can be done only if it prompts box for user to select the location.



Please help me friends.

Regards,
Shanmugam

[JoeM]

I do this a lot. I create a Form with a Text Box where the user can enter in the file path they want. Then, a command button runs the TransferText code, incorporating the value of the Text Box in the TransferText code.

[shanmugamgsn]

Thanks JoeM for your quick reply.

Can you give a sample DB or any tutorial link for this. This could be helpful for me

[shanmugamgsn]

But the soultion you gave is like user has to enter whole path in that text box right?
Can this be done by user by selecting.
Like a explorer window to select that path.
then i can store it in a vairable.

[JoeM]

You can do that, though it requires some additional VBA coding. I had used some code that I found a lot time of go. It is a ton of code, but you really just "plug and play".

I found something that looks a bit promising and is a bit shorter. Try this one out: http://www.vbaexpress.com/kb/getarticle.php?kb_id=284

[shanmugamgsn]

Thanks JoeM it worked out .........