Importing a 'non-standard' file type

[Emma Hope]

All,

We have a number (50+) of reports downloaded daily from a propriatary
database system, the files are named 123456.CR3 etc, they are essentially
delimited text files, to import them into Access we have to change the file
name of each one manually to 123456.txt etc and then the macro which imports
them into our database works fine. We make no other changes to the files.

Is there a way of importing these without having to manually change every
file name?

Thanks
Emma

 

[Ken Snell]

For security reasons, newer versions of ACCESS will not import or export
(via TransferText) text files with nonstandard files extensions -- the only
file extensions that can be used for text files for ACCESS to import them
are ".txt", ".csv", ".tab", and ".asc". To handle a ".cr3" file extension,
see these Knowledge Base articles:

ACC2000: Importing or Linking a Text File Fails for a File That Does Not
Have a Valid File Name Extension
http://support.microsoft.com/default.aspx?id=304206

ACC2000: "Can't Update. Database or Object Is Read-only" Error Message When
You Import or Export Files
http://support.microsoft.com/default.aspx?scid=kb;[LN];245407

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

(The renaming of the file is the preferred method.)


You can programmatically change the to-be-imported text file's extension
before the text file import is done. See Name statement in VBA help file.

Example:
Name "C:\FolderName\TextFileName.cr3" As
"C:\FolderName\TextFileName.txt"

The above VBA code can be run in a public function (in a regular module)
that is called by the "RunCode" ACCESS macro action.

 

[Clifford Bass via AccessMonster.com]

Hi Emma,

If they are all in one folder you can do something like this:

Shell "cmd /c ren ""C:\Some Folder\Some Subfolder\*.cr3"" ""*.txt""",
vbHide

Note that those are all quotes, not apostrophes.

Clifford Bass