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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul Dennis" <(E-Mail Removed)> wrote in message
news:A1162C92-3689-4ED8-B1A4-(E-Mail Removed)...
> 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?

Thanks

However having problems with strCurrFile = Dir(strCurrFolder & "*.*"), when
watching strCurrFile it brings back nothing?


"Douglas J Steele" wrote:

> 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
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Paul Dennis" <(E-Mail Removed)> wrote in message
> news:A1162C92-3689-4ED8-B1A4-(E-Mail Removed)...
> > 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?
>
>
>

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

"Paul Dennis" wrote:

> Thanks
>
> However having problems with strCurrFile = Dir(strCurrFolder & "*.*"), when
> watching strCurrFile it brings back nothing?
>
>
> "Douglas J Steele" wrote:
>
> > 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
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Paul Dennis" <(E-Mail Removed)> wrote in message
> > news:A1162C92-3689-4ED8-B1A4-(E-Mail Removed)...
> > > 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?
> >
> >
> >

Douglas,
How could I include subfolders and their files in this copy/move code?


"Douglas J Steele" wrote:

> 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
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Paul Dennis" <(E-Mail Removed)> wrote in message
> news:A1162C92-3689-4ED8-B1A4-(E-Mail Removed)...
> > 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?
>
>
>

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
http://I.Am/DougSteele
(no private e-mails, please)


"Zadok @ Port of Seattle" <Zadok @ Port of
(E-Mail Removed)> wrote in message
news:52D96908-84D7-4E58-9F37-(E-Mail Removed)...
> Douglas,
> How could I include subfolders and their files in this copy/move code?
>
>
> "Douglas J Steele" wrote:
>
>> 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
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Paul Dennis" <(E-Mail Removed)> wrote in message
>> news:A1162C92-3689-4ED8-B1A4-(E-Mail Removed)...
>> > 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?
>>
>>
>>