Access VBA problem - importing spreadsheets, with error handlin'

[CosmoPants]

Afternoon,

I've been trying to write something to bring in lots of Excel spreadsheets as tables. Some will be there all the time, and others may or may not be, so the code has to avoid falling over files it can't find. I wanted it to tell you which ones were found and imported, and which ones weren't found, which I thought wouldn't be too hard, but it's really kicking me up the bottom.

It sorta works, as long as the mising file is the first in the list, but the code gracelessly throws a wobbler otherwise. I'm quite new to VBA, so I'm probably doing something silly. It's supposed to add the filename and destination table of the files it found and didn't find to two strings, then spout up a message box containing first one string then the other, to let the user know.

My code looks like this at the moment:



Option Compare Database
Option Base 1
Option Explicit
Public Sub IMPORT_IMP_FILES()

Dim FileLoc As String
FileLoc = "C:\myfolders\"

Dim Folder As String
Folder = InputBox("Please enter a folder name, like '2011_07' ")
Dim SourceFile
SourceFile = Array("PFI_FOR_IMP.xls", _
"PFI_FOR_IMP2.xls", _
"PFI_FOR_IMP3.xls")

Dim DestinationTable
DestinationTable = Array("IMP_PFItest", _
"IMP_PFI2test", _
"IMP_PFI3test")
Dim SourceFileRange
SourceFileRange = Array("RawData!A:AC", _
"RawData!A:AC", _
"RawData!A:AC")
Dim Imp_files_ok As String
Dim Imp_files_failed As String
Dim FileString As String
Dim i As Integer
For i = 1 To UBound(SourceFile)

On Error GoTo ErrorHandler
looper:
FileString = FileLoc & Folder & "\" & SourceFile(i)
DoCmd.TransferSpreadsheet acImport, 8, DestinationTable(i), FileString, -1, SourceFileRange(i)
Imp_files_ok = Imp_files_ok & SourceFile(i) & " Imported to " & DestinationTable(i) & Chr(13)
Next i

MsgBox ("The following files were successfully imported:" & Chr(13) & Imp_files_ok)

MsgBox ("Errors occurred when attempting to import the following file(s), and the destination tables should be checked:" & Chr(13) & Imp_files_failed)

Exit Sub

ErrorHandler:
Imp_files_failed = Imp_files_failed & SourceFile(i) & " errored on import to " & DestinationTable(i)
i = 1 + 1
GoTo looper

End Sub
-----------------

Hope this makes sense. Any ideas?

 

[Norie]

If you just need to check for a files existence you can use Dir.

By the way the code as it is now might work if you change i=1+1 to i=i+1.

But you shouldn't be changing the loop variable i anyway.

Try this.

Option Compare Database
Option Base 1
Option Explicit

Public Sub IMPORT_IMP_FILES()
Dim FileLoc As String
Dim DestinationTable
Dim Folder As String
Dim SourceFile
Dim SourceFileRange
Dim Imp_files_ok As String
Dim Imp_files_failed As String
Dim FileString As String
Dim I As Integer
 
    FileLoc = "C:\myfolders\"

    Folder = InputBox("Please enter a folder name, like '2011_07' ")
 
    SourceFile = Array("PFI_FOR_IMP.xls", "PFI_FOR_IMP2.xls", "PFI_FOR_IMP3.xls")
 
    DestinationTable = Array("IMP_PFItest", "IMP_PFI2test", "IMP_PFI3test")
 
    SourceFileRange = Array("RawData!A:AC", "RawData!A:AC", "RawData!A:AC")
 
    For I = LBound(SourceFile) To UBound(SourceFile)
 
        FileString = FileLoc & Folder & "\" & SourceFile(I)
 
        ' check for file
        If Len(Dir(FileString)) <> 0 Then
 
            ' if file exists import
            DoCmd.TransferSpreadsheet acImport, 8, DestinationTable(I), FileString, -1, SourceFileRange(I)

            Imp_files_ok = Imp_files_ok & SourceFile(I) & " Imported to " & DestinationTable(I) & Chr(13)
 
        Else
        
            Imp_files_failed = Imp_files_failed & SourceFile(I) & " errored on import to " & DestinationTable(I)
            
        End If
    Next I
 
    MsgBox ("The following files were successfully imported:" & Chr(13) & Imp_files_ok)
 
    MsgBox ("Errors occurred when attempting to import the following file(s), and the destination tables should be checked:" & Chr(13) & Imp_files_failed)
 
End Sub

 

[CosmoPants]

That's absolutely brilliant, works a treat! Thanks muchly!