transfertext filename without .txt (1 Viewer)

[ascaife]

Hey guys,

Any help would be greatly appreciated.

I'm using a transfer text macro to create a text file for upload into a 3rd party system.

Problem is, the 3rd party system has pretty strange naming requirements and I need to create the file without the txt extension.

Does anyone know if this is possible, either within the macro or using VBA docmd transfertext.

I'm no VBA guru so if you could outline the code in full it would be greatly appreciated.

If it's VBA, could you also let me know how to add a pop up to request the filename.

Cheers,

Andrew

 

[ascaife]

Got it....

Not sure if this is the best method but it works for now:

Private Sub CommandButton_Click()
DoCmd.TransferText acExportFixed, "MySpecs", "qryMyQuery", "C:\FHM\VDU.txt", No
Dim fso As New Scripting.FileSystemObject
fso.MoveFile "C:\Sample\Sample.txt", "C:\Sample\" & InputBox("Enter Sample File Name:")
Set fso = Nothing
MsgBox "You have successfully created the Sample file in C:\Sample", vbInformation + vbOKOnly + vbDefaultButton2, "Well Done"
End Sub


In order for the FileSystemObject to work I had to tick "Microsoft Scripting Runtime" in tools, references.