Import csv files and rename them after import into 'imported' and 'failed'

[mirtomi]

Hi,

I have the following piece of code for importing the .csv files from a selected folder and then renaming them into 'imported & filename' if succesful and 'failed & filename' if import failes for whatever reason (bad formating, etc.).

The problem is that the first time it encounters a 'bad' file after another, instead of going again to the Error handler, the command

DoCmd.TransferText acImportDelim, , "Activitate", filename, True

gives me the default MS Access error, namely 2391. I would like it to go the the Error_handler again ang follow the course of actions and rename the files into 'failed' and 'imported'.

Here's the code:

 Sub ImportActivitate()
  Dim strFile As String 'Filename's
  Dim strFileList() As String ' File Array
  Dim intFile As Integer 'Number of files
  Dim filename As String, sFullName As String, sFilename As String
  
    DoCmd.SetWarnings False
  '--------------------------------------------------------------------------
  'This created a folder dialog picker from where to import
  'the excel files

'Directory selection
Dim path As String
Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Please select the import directory:"
    .AllowMultiSelect = False
    .InitialFileName = CurrentProject.path & "\"
    If .Show <> -1 Then Exit Sub
    path = .SelectedItems(1) & "\"
End With
    '--------------------------------------------------------------------------
    'Creates a look to build the file list

    strFile = Dir(path & "*.csv")

    While strFile <> ""

       'add files to the list
       intFile = intFile + 1
       ReDim Preserve strFileList(1 To intFile)
       strFileList(intFile) = strFile
       strFile = Dir()

    Wend

    'Check for files

   If intFile = 0 Then

      MsgBox "There are no files to import! Plese select another directory.", _
      vbExclamation + vbOKOnly, "No files found"
      GoTo SelectareDirector

   End If

    'cycle through the list of files
    For intFile = 1 To UBound(strFileList)
    filename = path & strFileList(intFile)
   
    
    'filename = sFullPath = "C:\dir\dir\dir\file.txt"
    sFullFilename = Right(filename, Len(filename) - InStrRev(filename, "\")) ' spre exemplu 01-09-2012.csv
    sFilename = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))         ' spre exemplu 01-09-2012
    
On Error GoTo Error_Handler
    DoCmd.TransferText acImportDelim, , "Activitate", filename, True
    Name filename As path & "imported_" & sFullFilename

Nextfile:
    Next intFile
    
    MsgBox "Import Complet!", _
    vbInformation + vbOKOnly, "Import complete"
    
    Exit Sub

Error_Handler:
        Dim response As Long
        response = MsgBox("MS Access a generat urmatoarea eroare:" & vbCrLf & vbCrLf & "Numar eroare: " & _
                    Err.Number & vbCrLf & "Sursa erorii: ImportTranzactii" & vbCrLf & "Descrierea erorii: " & _
                    Err.Description & vbCrLf & _
                    "Eroarea a aparut la importul fisierului " & sFullFilename & vbCrLf & _
                    "Daca apasati OK, importul fisierelor ramase va continua." & vbCrLf & _
                    "Daca apasati CANCEL, importul fisierelor va fi intrerupt." _
                    , vbCritical + vbOKCancel, "A aparut o eroare!")
                    
If response = vbCancel Then
    Exit Sub
        Else
        
        Name filename As path & "failed_" & sFullFilename
        Err.Clear
        GoTo Nextfile
        'Resume Next
End If
       
DoCmd.SetWarnings True
    
End Sub

Also, how can I make the routine stop cycling through the files after I renamed them all?

Many thanks for the help in advance!

 

[Ranman256]

ON Error does not need to be in the loop. You only run it once , usu as the 1st statement in the sub. You have it deep in the sub, which is ok, but its not needed over and over inside that loop.

On Error GoTo Error_Handler
    'cycle through the list of files
    For intFile = 1 To UBound(strFileList)

next, when you trap your error you must answer a question, this isnt needed everytime either, you can direct errrors that you know what to do

Error_Handler:
if Err = 2391 then 
  'do something here
else        
        response = MsgBox("MS Access a generat urmatoarea eroare:" & vbCrLf & vbCrLf & "Numar eroare: " & _

 

[spikepl]

Get the free SmartIndenter (google) and indent your code properly. It is very confusing to read it as is right now.

 

[spikepl]

BTW: For this type of thing what I do is not rename files but the ones that did get imported, I move into an Imported-folder, that I normally make as a subfolder of the folder form which the files come. This avoids renaming if I need to read them in again, and makes it easy to see what got read and what did not.