asked on

Uploading multiple data files

Can someone please tell me how to get this upload to work? I have put the code below (in my database is it a module). The issue is, I get the file uploaded 4 times (even though I only want it once) and if I have more than one file pending upload, it only process the first file.

Is it possible the issue is from the switchboard and not this module?

Any advice is greatly appreciated.
THANKS!

Public Results As Integer
Option Compare Database
 
Function ImportJPMCDebitTransactions()
    Dim strFile As String
    Dim strFolderName As String
    
    Dim fso, fldr, s
    Set fso = CreateObject("Scripting.FileSystemObject")
 
 
    
strFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process\"
strProcessedFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - Processed\"
strFile = Dir(strFolderName & "*.csv")
strFileCurrentlyProcessing = strFolderName & strFile
Set fldr = fso.GetFolder(strFolderName)
 
Do While (Len(strFile) > 0)
DoCmd.TransferText acImportDelim, "JPMC DEBITS Import Specification", "JPMC Debits", strFileCurrentlyProcessing
'set directory to look for next text file
   strFile = Dir
Loop
 
fso.CopyFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process", "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - Processed"
fso.DeleteFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process"
fso.CreateFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Debits - to process"
 
End Function

Open in new window

ASKER CERTIFIED SOLUTION

frankytee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LERNWebmaster

ASKER

It was so close! I just made a little bit of modifications and off it went!

Thanks for your help!!!

Function ImportJPMCCreditTransactions()
Dim strFile As String
    Dim strFolderName As String
    Dim strProcessedFolderName As String
    Dim strFileCurrentlyProcessing As String
    
    Dim fso, fldr, s
    'Set fso = CreateObject("Scripting.FileSystemObject")
    Set fso = CreateObject("Scripting.FileSystemObject")
      
    strFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process\"
    strProcessedFolderName = "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - Processed\"
    
    Set fldr = fso.GetFolder(strFolderName)
    For Each f In fldr.Files
        If Right(f.Name, 4) = ".csv" Then
            strFileCurrentlyProcessing = strFolderName & f.Name
            DoCmd.TransferText acImportDelim, "JPMC Credits Import Specification", "JPMC Credits", strFileCurrentlyProcessing
        End If
    Next f
    
     
    fso.CopyFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process", "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - Processed"
    fso.DeleteFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process"
    fso.CreateFolder "C:\Documents and Settings\rlschluk\Desktop\Cash Database\JPMC Credits - to process"
 
End Function

Open in new window