TransferText

[slyvsspy]

I run a macro that uses the "TransferText" function to export a query. I have to tell the function where to save the file exactly and with what name. How could I go about giving the user the ability to save the file with a different name? We are going to be exporting the query many times with different parameters and it isn't no good if it keeps saving it with the same filename.

Thanks

 

[catt]

I was able to import text, in a limited way , which may help you.


Dim FALL01 As String
Dim SPRING02 As String
Dim FALL02 As String
Dim SPRING03 As String
Dim SUMMER02 As String
Dim SUMMER03 As String
Dim FALL03 As String
Dim WINTERIM02 As String
Dim WINTERIM03 As String

FALL01 = "C:\SQR\FALL01.TXT"
FALL02 = "C:\SQR\FALL02.TXT"
SPRING02 = "C:\SQR\SPR02.TXT"
SPRING03 = "C:\SQR\SPR03.TXT"
SUMMER03 = "C:\SQR\SUM03.TXT"
SUMMER02 = "C:\SQR\SUM02.TXT"
WINTERIM02 = "C:\SQR\WIN02.TXT"
FALL03 = "C:\SQR\FALL03.TXT"
WINTERIM03 = "C:\SQR\WIN03.TXT"


DoCmd.SetWarnings False
' EMPTY OUT CHOOSE-S
DoCmd.OpenQuery "Q-CHOOSE-S EMPTY OUT", acNormal, acEdit
DoCmd.Close acQuery, "Q-CHOOSE-S EMPTY OUT"
' IMPORT CHOOSE-S
If [Forms]![F-USE TO REVIEW]![semester] = "200201" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", FALL01, False, ""
Else

If [Forms]![F-USE TO REVIEW]![semester] = "200203" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", SPRING02, False, ""
Else

If [Forms]![F-USE TO REVIEW]![semester] = "200301" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", FALL02, False, ""
Else

If [Forms]![F-USE TO REVIEW]![semester] = "200303" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", SPRING03, False, ""
Else

If [Forms]![F-USE TO REVIEW]![semester] = "200304" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", SUMMER03, False, ""
Else

If [Forms]![F-USE TO REVIEW]![semester] = "200204" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", SUMMER02, False, ""
Else

If [Forms]![F-USE TO REVIEW]![semester] = "200202" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", WINTERIM02, False, ""
Else

If [Forms]![F-USE TO REVIEW]![semester] = "200302" Then
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", WINTERIM03, False, ""


End If
End If
End If
End If
End If
End If
End If
End If



I chose ahead of time what the files would be called and created a string per above for every possibility that I need.

The user through a form, enters the pre-defined variable. That variable is then used through an "IF" command to select the proper path.

I hope this helps.

P.S. The above code is not complete, but it should give you a good idea on how to complete the task.

Judy

 

[Rob.Mills]

Instead of using TransferText, use the Outputto command. You can specify the kind of format you want and if you don't specify the filename and path then each time you run the macro it will prompt the user to enter the name.