[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Uploading multiple data files

Posted on 2009-02-09
2
Medium Priority
?
276 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:LERNWebmaster
2 Comments
 
LVL 19

Accepted Solution

by:
frankytee earned 750 total points
ID: 23596315
since you are using the filesystem object you dont need to use the below line
      strFile = Dir(strFolderName & "*.csv")
also
      'set directory to look for next text file
         strFile = Dir
is wrong, it should actuallly return an error if your variable strfile was declared as a string

i've also declared the variables in used in your function within the function to keep it as local variables. you may change if required.
you should also declare your variable types explictly, your code below actually declares them as variants
    Dim fso, fldr, s

see my code below
Function ImportJPMCDebitTransactions()
'http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q__24127058.html
    'ee question to fix
    Dim strFile As String
    Dim strFolderName As String
    Dim strProcessedFolderName As String
    Dim strFileCurrentlyProcessing As String
    
    Dim fso As FileSystemObject, fldr As folder, s
    Dim f As file
    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") 'remove
    'Do While (Len(strFile) > 0) 'remove
    '========================================================================
    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 DEBITS Import Specification", "JPMC Debits", strFileCurrentlyProcessing
        '========================================================================
        'below is wrong
        'set directory to look for next text file
        'strFile = Dir
        '========================================================================
        End If
    Next f
    
     
    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

0
 

Author Comment

by:LERNWebmaster
ID: 23652684
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

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question