Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Automatically moving file after importing a csv file.

Posted on 2008-09-03
6
Medium Priority
?
649 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:jammin140900
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22384712
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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 22384714
jammin140900

Try this:
Name "C:\Upload\Data\Import\ImportRegistered.csv" As "C:\Upload\Data\Archive\" & "ImportRegistered" & Format(Date, "mmddyy") & ".csv"

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22384726
jammin140900,

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

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

JeffCoachman
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 9

Expert Comment

by:csk_73
ID: 22384739
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

0
 

Author Closing Comment

by:jammin140900
ID: 31493046
That was brilliant! Thanks mate...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22385332
;-)

Thanks but you could have split the points.

All three post would have worked.
;-)

JeffCoachman
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question