Create Directory / Copy Files / Delete Files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking to create a Module in Access that will create a directory based
on the current month/ year and then move all files from one direct to it.

ie create directory "C:\reporting\2006 Jan"
copy "C:\reporting\data\*.*" "C:\reporting\2006 Jan"
delete "C:\reporting\data\*.*"

Do you know the syntax please?
Also want it so it doesn't prompt for confirmation?
 
To create a directory, use MkDir.

Assuming C:\Reporting already exists, you'd use:

MkDir "C:\Reporting\" & Format(Date(), "yyyy mmm")

To move files, you use the Name statement. Unfortunately, you can't use wild
cards with the Name statement (nor with the FileCopy statement, although you
can with the Kill statement). To have a VBA-only solution to your problem,
you could use something like:

Dim strCurrFile As String
Dim strCurrFolder As String
Dim strNewFolder As String

strCurrFolder = "C:\Reporting\Data\"
strNewFolder = "C:\Reporting\2006 Jan\"
strCurrFile = Dir(strCurrFolder & "*.*")
Do While Len(strCurrFile) > 0
Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile
strCurrFile = Dir()
Loop
 
Thanks

However having problems with strCurrFile = Dir(strCurrFolder & "*.*"), when
watching strCurrFile it brings back nothing?
 
Sorry my fault it does work, I just added a bit and missed a \ hence my code
is now:

MkDir "C:\eESM Reporting\Last Month\" & Format(DateAdd("m", -1, Date), "yyyy
mmm")

Dim strCurrFile As String
Dim strCurrFolder As String
Dim strNewFolder As String

strCurrFolder = "C:\eESM Reporting\Access Data\"
strNewFolder = "C:\eESM Reporting\Access Data\" & Format(DateAdd("m", -1,
Date), "yyyy mmm") & "\"
strCurrFile = Dir(strCurrFolder & "*.*")
Dir
Do While Len(strCurrFile) > 0
Name strCurrFolder & strCurrFile As strNewFolder & strCurrFile
strCurrFile = Dir()
Loop


This creates a sub directort for last month and moves all the files to that
directly as a back up and ready for this months extract.

many many thanks
 
That's a bit more involved.

To do it using VBA, one way is to create a recursive routine that uses the
Dir function with the vbDirectory parameter to determine all of the
subdirectories and store them in an array, then pass each of the
subdirectories to the function. (The reason you need to store them all in an
array is that you cannot call the Dir function from within another Dir
function).

Other approaches would be to use FSO (File System Objects) or APIs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Zadok @ Port of Seattle" <Zadok @ Port of
(e-mail address removed)> wrote in message
 
Back
Top