Import Files and Move Files from Folder

[Guest]

I am a complete novice at using VB programming. I found some code to import
files that are in a folder regardless of their filenames. I am going to set
this up to run on the database startup and use taskmanager to startup the
database at night.

My problem is that this code will pickup all files in that folder. Since it
does this, I now need some code that I can add that will move all files from
the folder to an archive directory. Does anyone have code that will pick up
all files from one directory and drop them to another directory? Thanks in
advance...

 

[Douglas J. Steele]

Presumably your code is retrieving the file names file by file for import
purposes. After you've read the contents of the file, you can use the Name
statement to move the file to another location (provided both locations are
on the same drive):

Name oldpathname As newpathname

 

[Guest]

I don't think that will work since the filename will always change. I have a
set up where some information is downloaded onto a drive but the filename
changes daily. The code I am using (see below) picks up every file in a
folder and loads it into the database. Is there a way to do below if there
are multiple files in the folder that don't have the same filename everytime?

Private Sub ImportAllFiles()

'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.

Dim strfile As String

Dim tblName As String

ChDir ("S:\Onfield (old Logo folder)\CLAIMS\DMallasch\UPSCompleteOutbound")

strfile = Dir("*.csv")

Do While Len(strfile) > 0

'table name same as file name without ext

tblName = "tblCurrent UPS Information"

DoCmd.TransferText acImportDelimited, "Current UPS Information Spec",
tblName, strfile, True

strfile = Dir

Loop

End Sub

 

[Douglas J. Steele]

My suggestion doesn't require the filename to be the same each time.

Now that I've seen your code, I can suggest something more concrete. In the
following, each file will be copied to a folder named S:\Archives\yyyymmdd\
(where yyyymmdd will be that day's date). Note that I removed your ChDir
statement. You're MUCH safer off being explicit with your code, rather than
relying on the current directory.

Private Sub ImportAllFiles()

'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.

Dim strArchiveFolder As String
Dim strFolder As String
Dim strFile As String
Dim tblName As String

strArchiveFolder = "S:\Archives\" & Format(Date(), "yyyymmdd") & "\"
strFolder = "S:\Onfield (old Logo
folder)\CLAIMS\DMallasch\UPSCompleteOutbound\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
'table name same as file name without ext
tblName = "tblCurrent UPS Information"
DoCmd.TransferText acImportDelimited, "Current UPS Information Spec",
_
tblName, strFolder & strFile, True
Name strFolder & strFile As strArchiveFolder & strFile
strfile = Dir
Loop

End Sub

 

[Guest]

I get runtime error 53 'File Not Found' on the Name As line. See code below.
Any suggestions? I am running the code in the VB application in MS Access.
Thanks!

Private Sub ImportAllFiles()

'procedure to import all files in a directory and archive them to a seperate
folder.
'assumes they are all the correct format for an ASCII delimited import.

Dim strArchiveFolder As String
Dim strFolder As String
Dim strFile As String
Dim tblName As String

strArchiveFolder = "S:\Onfield (old Logo
folder)\CLAIMS\DMallasch\Archives\" & Format(Date, "yyyymmdd") & "\"
strFolder = "S:\Onfield (old Logo
folder)\CLAIMS\DMallasch\UPSCompleteOutbound\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
tblName = "tblCurrent UPS Information"
DoCmd.TransferText acImportDelimited, "Current UPS Information Spec",
tblName, strFolder & strFile, True
Name strFolder & strFile As strArchiveFolder & strFile
strFile = Dir
Loop

End Sub

 

[Douglas J. Steele]

Does the folder pointed to by strArchiveFolder exist? It must for the Name
statement to work: Name cannot create folders.

After the statement

strArchiveFolder = "S:\Onfield (old Logo
folder)\CLAIMS\DMallasch\Archives\" & Format(Date, "yyyymmdd") & "\"

try adding:

If Len(Dir(strArchiveFolder, vbDirectory)) = 0 Then
MkDir strArchiveFolder
End If

 

[Guest]

Douglas:

Thanks for your help. The problem was that I did not have the date folders
set up. The filenames have the date on them already so not really needed
anyway. I changed the code to take out the date sequence in the
strArhciveFolder statement. It works perfectly now. Thanks!

Private Sub ImportAllFiles()

'procedure to import all files in a directory and archive them to a seperate
folder.
'assumes they are all the correct format for an ASCII delimited import.

Dim strArchiveFolder As String
Dim strFolder As String
Dim strFile As String
Dim tblName As String

strArchiveFolder = "S:\Onfield (old Logo
folder)\CLAIMS\DMallasch\Archives\"
strFolder = "S:\Onfield (old Logo
folder)\CLAIMS\DMallasch\UPSCompleteOutbound\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
tblName = "tblCurrent UPS Information"
DoCmd.TransferText acImportDelimited, "Current UPS Information Spec",
tblName, strFolder & strFile, True
Name strFolder & strFile As strArchiveFolder & strFile
strFile = Dir
Loop

End Sub