MOVE *.xls files AFTER mass .xls imports!

[Jason]

Hello.. and thank you for participating in this awesome site. I have a problem that I hope someone has a quick solution for as I am pressed for time.

The following code searches a directory and imports all data from all .xls files in that directory. This works fantastic. The problem is that if I click the command button more than once, duplicates of all imported data is entered.

What I NEED to do is simply ammend the code to MOVE all .xls files to a subdirectory named "archive". That's it. I was thinking this simply be done at the end of the function? Thanks in advance, and have a great day!


Private Sub Command24_Click()
LocateFile ("*.xls") ' calls the LocateFile function and uses * for wildcard for strfilename to match.
End Sub
Function LocateFile(strFileName As String)
Dim vItem As Variant
With Application.FileSearch
.FileName = strFileName
.LookIn = "C:\mydirectory" 'path to xls files
.SearchSubFolders = False 'leave as false, don't want to search in lower folders.
.Execute
For Each vItem In .FoundFiles
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", vItem, True, ""
Next vItem

'maybe include move instruction here?
End With
End Function



*THHANKS in advance

 

[Ted]

Hi Jason,

This is the way that I have done this in the past (of
course you will need to adjust the file paths). As
always there are probably other ways to do this.

Dim fso As Object, strSourceFiles As String,
strDestFolder As String

Set fso = CreateObject("Scripting.FileSystemObject")

strSourceFiles = "C:\mydirectory\*.xls"
strDestinationFolder = "C:\mydirectory\Archive\"

fso.movefile strSourceFiles, strDestinationFolder

That should be all that it takes. If it doesn't work, or
if you want more info on this method, you can search VB
help for "MoveFile". Let me know how it turns out.

-Ted

-----Original Message-----
Hello.. and thank you for participating in this awesome

site. I have a problem that I hope someone has a quick
solution for as I am pressed for time.

The following code searches a directory and imports all

data from all .xls files in that directory. This works
fantastic. The problem is that if I click the command
button more than once, duplicates of all imported data is
entered.

What I NEED to do is simply ammend the code to MOVE

all .xls files to a subdirectory named "archive". That's
it. I was thinking this simply be done at the end of the
function? Thanks in advance, and have a great day!

Private Sub Command24_Click()
LocateFile ("*.xls") ' calls the LocateFile

function and uses * for wildcard for strfilename to match.

End Sub
Function LocateFile(strFileName As String)
Dim vItem As Variant
With Application.FileSearch
.FileName = strFileName
.LookIn = "C:\mydirectory" 'path to xls files
.SearchSubFolders = False 'leave as false, don't

want to search in lower folders.

.Execute
For Each vItem In .FoundFiles
DoCmd.TransferSpreadsheet acImport,

acSpreadsheetTypeExcel9, "MyTable", vItem, True, ""

 

[Nikos Yannacopoulos]

Jason,

Try this bit of code:

Sub Move_Files()
Dim sourceFolder As String
Dim destFolder As String
Dim fs, fldr, fls
sourceFolder = "C:\MyDirectory"
destFolder = "C:\MyDestDirectory"
Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder(sourceFolder)
Set fls = fldr.Files
For Each f In fls
If Right(f.Name, 4) = ".xls" Then
newfile = Replace(f, sourceFolder, destFolder)
fs.MoveFile f, newfile
End If
Next
End Sub

HTHm
Nikos

Hello.. and thank you for participating in this awesome site. I have a

problem that I hope someone has a quick solution for as I am pressed for
time.

The following code searches a directory and imports all data from all .xls

files in that directory. This works fantastic. The problem is that if I
click the command button more than once, duplicates of all imported data is
entered.

What I NEED to do is simply ammend the code to MOVE all .xls files to a

subdirectory named "archive". That's it. I was thinking this simply be done
at the end of the function? Thanks in advance, and have a great day!

Private Sub Command24_Click()
LocateFile ("*.xls") ' calls the LocateFile function and uses * for

wildcard for strfilename to match.

 

[Jason]

Ok I am going to be a total tard here and ask a newbie question... :

How am I to implement this code into my existing routine? Please do explain... I would be very appreciative

~jason

 

[Jason]

Again, I managed to find a solution.... hmm...

SOLUTION:


Private Sub Command24_Click() 'command button
LocateFile ("*.xls")
End Sub
Function LocateFile(strFileName As String) 'search and import function
Dim vItem As Variant
With Application.FileSearch
.FileName = strFileName
.LookIn = "J:\mydir"
.SearchSubFolders = False 'leave as false, don't want to search in lower folders.
.Execute
For Each vItem In .FoundFiles
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTable", vItem, True, ""
Next vItem
End With
MoveFiles ("J:\mydir")
End Function

Function MoveFiles(sFromPath As String) 'move files to the archive directory
Dim sFile As String


If Right(sFromPath, 1) <> "\" Then
sFromPath = sFromPath & "\"
End If

sFromPath = sFromPath & "*.xls"

' Loop through all files.
sFile = Dir$(sFromPath)
Do While Len(sFile) > 0
FileCopy Left$(sFromPath, Len(sFromPath) - 5) & sFile, "J:\mydir\archive\" & sFile
Kill Left$(sFromPath, Len(sFromPath) - 5) & sFile
sFile = Dir$
Loop
End Function


:: This will search a directory for "*.xls", update a table with all contents of each excel file, and move all imported files to a /archive directory.

Thanks a mill!

 

[Ted]

Hi Jason,

Choose either of the previous methods that were posted.
Combine the Dim statement with your dim statement. Then
add the remaining code between your End With and End Sub
statements (because you don't want to move the files
until after they have been imported. That should do the
trick.

-Ted

-----Original Message-----
Ok I am going to be a total tard here and ask a newbie question... ::

How am I to implement this code into my existing

routine? Please do explain... I would be very
appreciative.

 

[Nikos Yannacopoulos]

Jason,

If your archive directory is on the same HDD as the import one, to copy the
files and then delete the oiginals is quite an overkill, when you can just
move them... and quite slower too.

HTH,
Nikos

Again, I managed to find a solution.... hmm...

SOLUTION:


Private Sub Command24_Click() 'command button
LocateFile ("*.xls")
End Sub
Function LocateFile(strFileName As String) 'search and import function
Dim vItem As Variant
With Application.FileSearch
.FileName = strFileName
.LookIn = "J:\mydir"
.SearchSubFolders = False 'leave as false, don't want to search in lower folders.
.Execute
For Each vItem In .FoundFiles
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTable", vItem, True, ""
Next vItem
End With
MoveFiles ("J:\mydir")
End Function

Function MoveFiles(sFromPath As String) 'move files to the archive directory
Dim sFile As String


If Right(sFromPath, 1) <> "\" Then
sFromPath = sFromPath & "\"
End If

sFromPath = sFromPath & "*.xls"

' Loop through all files.
sFile = Dir$(sFromPath)
Do While Len(sFile) > 0
FileCopy Left$(sFromPath, Len(sFromPath) - 5) & sFile, "J:\mydir\archive\" & sFile
Kill Left$(sFromPath, Len(sFromPath) - 5) & sFile
sFile = Dir$
Loop
End Function


:: This will search a directory for "*.xls", update a table with all

contents of each excel file, and move all imported files to a /archive
directory.

 

[Jason]

Dude.. what are you talking about?

This is a move, not a delete... lol.

 

[Nikos Yannacopoulos]

Sorry, this answer was intended for another posting!

 

[Nikos Yannacopoulos]

OK, you got me there for a minute... I was posting against the right
posting. Look at your code again:

FileCopy Left$(sFromPath, Len(sFromPath) - 5) & sFile,
"J:\mydir\archive\" & sFile
Kill Left$(sFromPath, Len(sFromPath) - 5) & sFile

You are copying to the new directory, and then deleting the original! There
is a HUGE difference between this and moving!
In the first case, you are writing the whole file again in a different place
on the disk, and then deleting the original.
In the second case, you are not touching the file at all, you are just
changing its directory listing in the file allocation table (or whatever the
equivalent is called for each file system).

Nikos