Copy and rename a text file

D

dan dungan

Hi,

Using Access 2000 and windows xp, I'm using the following procedure to
import text files to access:
___________________________________________________
Private Sub ImportAll()

Dim strFile As String

Const conFolder As String = "K:\Customer Service\Quote\"

strFile = Dir(conFolder & "*.txt")

Do While strFile <> ""

DoCmd.TransferText acImportDelim, "Data Import Specification",
"tblData", _
conFolder & strFile

Kill conFolder & strFile

strFile = Dir

Loop

End Sub
____________________________________________________

To create a backup, I'm manually renaming the files NBdata.txt and
MBdata.txt to NBdataMMDDYY.txt and MBdataMMDDYY.txt. Then, I copy them
to the backup folder.

I need to automate these tasks.

I found the function FileCopy SourceFile, DestinationFile

Do I need to put this inside the loop? If so, I don't know the proper
sytnax to create a unique file name if a file has already been created
the same day. (The users may run the process more often than once a
day.

I appreciate any advice.

Thanks,

Dan Dungan
 
D

dan dungan

Hi,

Ok. I guess no one responded because I had made no attempt at coding.
(See below.)

So, I don't understand why the following code files on the line

FileCopy SourceFile, DestinationFile

This is what I'm seeing in the immediate window:

K:\Customer Service\Quote\Backup\Data\2008\MBdata011508.txt

Any suggestions why this procedure won't copy, rename and paste the
file in the appropriate drive?

Thanks,

Dan

Sub CopyRename()
Dim strFile As String
Dim DestFile As String
Dim SourceFile, DestinationFile
Dim FileDate As String

Const conFolder As String = "K:\Customer Service\Quote\"
Const DestFolder As String = "K:\Customer Service\Quote\Backup\Data
\2008\"

strFile = Dir(conFolder & "*.txt")
SourceFile = strFile
DestFile = DestFolder & Left(strFile, InStr(1, strFile, ".", 1) -
1) & FileDate & ".txt"
FileDate = Format(Date, "mmddyy")
DestinationFile = DestFile
Debug.Print DestinationFile
Do While strFile <> ""
FileCopy SourceFile, DestinationFile


strFile = Dir

Loop

End Sub
 
D

Douglas J. Steele

Actually, you're doing the file renaming in the wrong place as well.

Sub CopyRename()
Dim strFile As String
Dim DestFile As String
Dim SourceFile, DestinationFile
Dim FileDate As String

Const conFolder As String = "K:\Customer Service\Quote\"
Const DestFolder As String = "K:\Customer
Service\Quote\Backup\Data\2008\"

strFile = Dir(conFolder & "*.txt")
Do While strFile <> ""
SourceFile = strFile
DestFile = DestFolder & Left(strFile, InStr(1, strFile, ".", 1) -1) &
FileDate & ".txt"
FileDate = Format(Date, "mmddyy")
DestinationFile = DestFile
Debug.Print DestinationFile
FileCopy conFolder & SourceFile, DestinationFile
strFile = Dir
Loop

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top