I'm uploading multiple "xls" files and delete when complete... I would like
to move files to an archive location then delete... below is the code I'm
using... Any help would be appreciate it.. thanks

Function ImportPFFS_Flat()
Set fs = Application.FileSearch
With fs
.LookIn =
"\\NASWin1.ad.wellcare.com\Dept\MemberServices\CommandCenterReport\Files2Upload\PFFS\"
.Filename = "WCARE_DAILY_CONTACT_LOG_2**.xls"
If .Execute > 0 Then
importfilecount = .Foundfiles.Count

For i = 1 To .Foundfiles.Count
Dim strFile As String
Dim strPath As String
Dim stDocName As String


'Loop through the string & import the files
'
DoCmd.Echo False, ""
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, , "xls_PFFS_Direct", _
.Foundfiles(i), True, ""
DoCmd.Echo True, "Import Completed"
DoCmd.SetWarnings True
Kill .Foundfiles(i)
'FileDateTime (.Foundfiles(i))
Next i
Else
Exit Function

On Error GoTo ImportMacro_Err


End If
End With
ImportMacro_Exit:
Exit Function

ImportMacro_Err:
MsgBox Error$
Resume ImportMacro_Exit

End Function

Check out the FileCopy (Source, Destination) function. You should be able to
include this in your loop to copy the files to a specified archive directory.
Just keep in mind that you need full paths for the Source and Destination
(the Destination needs to have the filename and extension to save as, not
just the folder to save to).


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery


"THE_RAMONES" wrote:

> I'm uploading multiple "xls" files and delete when complete... I would like
> to move files to an archive location then delete... below is the code I'm
> using... Any help would be appreciate it.. thanks
>
> Function ImportPFFS_Flat()
> Set fs = Application.FileSearch
> With fs
> .LookIn =
> "\\NASWin1.ad.wellcare.com\Dept\MemberServices\CommandCenterReport\Files2Upload\PFFS\"
> .Filename = "WCARE_DAILY_CONTACT_LOG_2**.xls"
> If .Execute > 0 Then
> importfilecount = .Foundfiles.Count
>
> For i = 1 To .Foundfiles.Count
> Dim strFile As String
> Dim strPath As String
> Dim stDocName As String
>
>
> 'Loop through the string & import the files
> '
> DoCmd.Echo False, ""
> DoCmd.SetWarnings False
> DoCmd.TransferSpreadsheet acImport, , "xls_PFFS_Direct", _
> .Foundfiles(i), True, ""
> DoCmd.Echo True, "Import Completed"
> DoCmd.SetWarnings True
> Kill .Foundfiles(i)
> 'FileDateTime (.Foundfiles(i))
> Next i
> Else
> Exit Function
>
> On Error GoTo ImportMacro_Err
>
>
> End If
> End With
> ImportMacro_Exit:
> Exit Function
>
> ImportMacro_Err:
> MsgBox Error$
> Resume ImportMacro_Exit
>
> End Function
>

"dymondjack" <dymondjack at hot mail dot com> wrote in message
news136BB51-EBAB-41C2-8EFD-(E-Mail Removed)...
> Check out the FileCopy (Source, Destination) function. You should be able
> to
> include this in your loop to copy the files to a specified archive
> directory.
> Just keep in mind that you need full paths for the Source and Destination
> (the Destination needs to have the filename and extension to save as, not
> just the folder to save to).


Note that if your goal is to copy a file from one location to another, and
then delete the original file, you can use the Name statement to move the
file in one step, rather than copying it and deleting it. I *think* that if
the file is being moved to a new location on the same hard disk, it just
updates the directory entry without moving any data, but it also works to
move the file from one drive to another.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)