CODEWITHSUNDEEP
ms-accessvba
Steve O'Dea
Steve O'Dea

Reputation: 107

MS Access VBA Data import for non-std file extensions

I am struggling to find the code I need to import a file into an access table.

It is a straight forward text file import however, the source file comes from a third party system and therefore the file extension is not the usual .txt file extension.

Instead the file is presented as ".ZZ;1"

This means that in order to import into access, I have to manually change the file extension before performing the import.

I wonder if there was a way to import the file (using VBA) with its given extension?

Or perhaps there is a piece of code which will allow me to change the file extension to something that access can read before I run my import script.

Any help or direction would be appreciated.

Upvotes: 1

Views: 815

Answers (1)

Andre
Andre

Reputation: 27644

You can save yourself some grief by renaming the file to a standard extension (.txt or .csv) before importing it.

To rename: use the Name Statement

e.g.

strNewName = Replace(strPathFile, ".ZZ;1", ".txt")
Name strPathFile As strNewName

or if you don't want to change the original file, use the FileCopy Function, or FileSystem.CopyFile

Upvotes: 2

Related Questions