Using a macro to export a table to dilimeted test

[bravo6]

I'm reposting this because the only answer I received dealt with VBA. I need to get this done through an Access Macro. Unless someone can what me through VBA.

I'm trying to use a macro to export a table to dilimeted text. It works but will only accept .txt as the output file extension. It would be great if I could get it to export to filename.kml. Is there a way to do this or to subsequently have a macro change the file name?

As always, thanks to you folks on this forum. You have saved me many times!

Dave

 

[MazeWorX]

in vba you can rename a file

Dim FileName AS String
Dim NewFileName AS String

Filename = "C:\filelocation\YourFileName.txt"
NewFileName = "C:\filelocation\YourFileName.kml"

Name FileName As NewFileName

HTH

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[bravo6]

But how do I open the macro in VBA?

 

[MazeWorX]

DoCmd.RunMacro "macroname", repeatcount, repeatexpression

repeatcount: A numeric expression that evaluates to an integer, which is the number of times the macro will run

repeatexpression: A numeric expression that's evaluated each time the macro runs. When it evaluates to False (0), the macro stops running.

both are optional

Private Sub cmdExport_Click()

Dim FileName As String
Dim NewFileName As String

DoCmd.RunMacro "My Macro"

FileName = "C:\filelocation\YourFileName.txt"
NewFileName = "C:\filelocation\YourFileName.kml"
Name FileName As NewFileName

End Sub

untested

HTH

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[sandymcrae]

Use a 'TransferText' Action with Action Arguments like these:
Transfer Type: Export Fixed Width
Specification Name: BACSOut
Table Name: qryBACSOut
File Name: ="\\servername\foldername\" & [Forms]![frmMenu]![user] & ".txt"
Has Field Names: No
HTML Table Name:
Code Page:

In this case the file suffix is .txt but you could call it what you want. This example also uses a field value to give a file name such as \\servername\foldername\dave.txt.