asked on

Automatically moving file after importing a csv file.

Hi Experts,

I've been working with importing a csv file into MS Access and all that is working beautifully. The last thing I need to do in this import is I want to move the file once it has been processed into a archive directory to clean it up nicely. This upload imports two csv files individually and again, I want it to be moved after import.

In terms of the specifics:

File Names: ImportRegistered.csv and ImportNotRegistered.csv
Initial location will be : C:\Upload\Data\Import (Both new files to be placed in this folder.)
After Import location of both files to move to will be: C:\Upload\Data\Archive

I should also ask if their a way to append the file name before moving it to archive too with say the date before or after the filename eg- ImportRegistered040908.csv or even 040908ImportRegistered.csv

If I don't do that, then the archive will probably overwrite the previous weeks file as the name would be the same.

As I have little idea of VBA, could you please help me with the code to:
1.Rename the file (if this is too complicated, I will leave this part!)
2. Move the files from the input to the archive directory.

Thank you,
Jammin

peter57r

Put this into a standard module.
If you want to do this from a button add a button (close the wizard immediately if it starts).
Go to On Click Event Procedure and enter (between the Sub and the End Sub statements)
movecsv

Sub Movecsv()
Dim Oldfile, NewFile
Oldfile = "C:\Upload\Data\Import\ImportRegistered.csv"
NewFile = ""C:\Upload\Data\Import\ImportRegistered" & format(date(),"yyyymmdd") & ".csv"
Name Oldfile as Newfile
Oldfile = "C:\Upload\Data\Import\ImportNotRegistered.csv"
NewFile = ""C:\Upload\Data\Import\ImportNotRegistered" & format(date(),"yyyymmdd") & ".csv"
Name Oldfile as Newfile
End sub

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

jammin140900,

The "Name" command allows you to "Move" the file.

So you are actually "Renaming" the file to a new folder.
;-)

JeffCoachman

csk_73

Hi,

for moving files I use this functions, I have them in a module

Private Declare Function DeleteFile Lib "kernel32" Alias "DeleteFileA" (ByVal lpFileName As String) As Long
Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long
 
Function fFileCopy(SourceFile As String, DestinationFile As String, error_if_destination_exists As Boolean) As Long
 
On Error GoTo Err_Proc
 
Dim ok As Boolean
 
    If CopyFile(SourceFile, DestinationFile, error_if_destination_exists) Then
        ok = True
    Else
        ok = False
    End If
 
Exit_Proc:
    Exit Function
    fFileCopy = ok
Err_Proc:
    ok = False
    Resume Exit_Proc
End Function
 
Function fDeleteFile(FileToDelete As String) As Boolean
 
On Error GoTo Err_Proc
     
     Dim ok As Boolean
     
     ok = (IIf(DeleteFile(FileToDelete) = 0, False, True))
 
Exit_Proc:
    Exit Function
    fDeleteFile = ok
Err_Proc:
    ok = False
    Resume Exit_Proc
 
End Function

Open in new window

jammin140900

ASKER

That was brilliant! Thanks mate...

Jeffrey Coachman

;-)

Thanks but you could have split the points.

All three post would have worked.
;-)

JeffCoachman