Access 2007 - Export Table to Text File w/ unrecognized extensions

[Mike G - DC]

Folks – I need a little help trying to export tables to text files with
extensions that are not recognized by Access, .cmv & .cmo. I’ve tried,
without luck, to implement the registry update referenced within the Access
2000 support file listed below. I’m either not implementing the registry
update/macro correctly or the work around does not apply to Access 2007.

ACC2000: How to Import a Text File That Has an Extension That Access Does
Not Recognize
http://support.microsoft.com/?id=306144

I appreciate your ideas.
- mike

 

[Ken Snell MVP]

Export to a .txt file, then use the Name statement in VBA code to change the
extension on the file.

 

[Mike G - DC]

Thanks, that works. I'm new to VBA but was able to figure out how to create
the function and include the step within my export macro using the OpenModule
Action. This leads to another question though.

My initial TransferText action included an expression within the File Name
argument to append a date/time stamp. In using the Name Statement though, it
looks like input side of the statement must remain constant, something like
export.txt. Is there a way to append some additional VBA code to the output
side of name statement to include the system date/time?

Thanks again for the initial answer. Any additional help is greatly
appreciated.
- mike

 

[Douglas J. Steele]

There's no restriction that the input side remain constant.

Dim strOldName As String
Dim strNewName As String

strOldName = "C:\Folder\File" & Format(Date, "yyyymmdd") & ".abc"
strNewName = "C:\Folder2\File" & Format(Date, "yyyymmdd") & ".txt"
If Len(Dir(strOldName)) > 0 Then
Name strOldName As strNewName
End If

(the Len(Dir(strOldName)) check ensures that the file actually exists before
you try renaming it)

 

[Mike G - DC]

Excellent! Works perfectly. Thank you.

Also, for those who may reference this discussion down the road, I'm using
the "RunCode" action within my macro to run this public function rather than
the "OpenModule" action referenced within my second post.

Thanks again for the advice.
- mike