Export table from Access to Lotus space del

[SalGal]

I need to export three tables to the Lotus space-delimited format (there is no file extension). The files will be uploaded to a mainframe. Is it possible to:

1. Export the tables into the correct format directly (I've been exporting them to .txt then deleting the file extension)
2. Name a file with dots in the name? These need to be named (for example) PAY.BR05.P333456

I have tried both through a macro and with VB using DoCmd.TransferText.

Thanks.

 

[JerryKlmns]

There is the Name statement syntax

Name oldpathname As newpathname

 

[SalGal]

That sounded like it would work - however, I haven't been successful. The code skips to the error trap when it reaches this line:

Name "c:\PAYBR05P227904.txt" As "c:\PAY.BR05.P227904"

Knowing me, I'm missing something very obvious. . .

 

[lewds]

Hi ....

The Name Statement is not available in VBA

Issue a Shell Command like...

Dim lRet
lRet = Shell("cmd /c ren c:\PAYBR05P227904.txt PAY.BR05.P227904")

cmd /c tells the command processor to execute the following string

ren is the rename command
c:\ is the Drive and path of the file
The file will be renamed in the same location

The Copy command can also be used, to place a copy of the file in another location with a new name...

lRet = Shell("cmd /c Copy c:\PAYBR05P227904.txt d:\PAY.BR05.P227904")

 

[PHV]

The Name Statement is not available in VBA
REALLY ?

 

[lewds]

Hi...

PHV

AS usual, you are correct. The Name Statement can be used to rename external files. I just didn't look closely enough at Help.

 

[JerryKlmns]

SalGal

There is also Scripting.FileSystemObject

Dim fso As Object 'Scripting.FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile  "c:\PAYBR05P227904.txt" , "c:\PAY.BR05.P227904"
fso.DeleteFile  "c:\PAYBR05P227904.txt" 
Set fso = Nothing

 

[SalGal]

Thanks for the suggestions - have hit a storm here and will try again in a day or so and report back.

SG

 

[SalGal]

Success! The Scripting.FileSystemObject worked like a charm! Many thanks - I will definite use this again.

SG