Import .lis files as .txt

[catt]

Hi:

I import several flat files from the network daily, all of these files have a .lis extension. Currently I open up a dos window and copy the files from .lis to .txt. I would like to rename these files through VBA, before importing them into Access.

Is this possible? I tried looking into the Replace funtion and the Rename function(?) but I don't understand them.

Thank You,


Judy

 

[RV]

Not sure what you're asking.

1) do you want to change the extension and keep the .lis files?
2) do do you want to change the extension and don't keep the .lis files?

And no, Replace and Rename is meant for (Access) database objects only.
So these won't help you.
There's a function called Name, depends on what you want exactly...

RV

 

[catt]

Hi RV;

The original files are in a directory in my computer, (I run an Oracle process that produces the files in my PC with a .lis extension).

I created a module the imports the file (after renaming the file with a new extension (.txt), by going into a DOS window and using the copy command)

I would like to be able to rename the file through VBA, and then import the file.

example;

Access would go to the specific directory c:\sqr\win03.lis and through magic rename it to c:\sqr\win03.txt.

The next line would be the transfer database process which doesn't like .lis files, where Access imports the file and process the data.

I thought I read somewhere that there is a way to open up a window? process information outside of Access and then Access continues its process.

Does this make sense?

Thank You,


Judy

 

[Pat Hartman]

Look up the Name Statement in help.

 

[catt]

Hi Pat;

Found it.

Name Statement Example

This example uses the Name statement to rename a file. For purposes of this example, assume that the directories or folders that are specified already exist. On the Macintosh, “HD:” is the default drive name and portions of the pathname are separated by colons instead of backslashes.

Dim OldName, NewName

OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.

Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.

Thank You


Judy

 

[fasternu]

FYI You don't have to rename the lis file as txt file in order for access to import it. You can open the .lis file if you specify the file naem e.g. file.lis when getting external data or if you are using a macro to import the info you can use macr to TransferText and then put in file path e.g. c:\file.lis

 

[Pat Hartman]

fasternu, an update to Jet a few years ago now prevents this. You either need to modify the registry to force Jet to accept all extensions or you need to rename text files to an extension that Jet finds acceptable.

One of Microsoft's truely STUPID (are you listening out there Bill?) ideas.