Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Error stops imort (2002)

    Hi!

    I'm using the following code to import multiple excel spreadsheets into a database:

    'Imports all excel files residing in designated folder

    Dim strFolder As String
    Dim strFile As String
    strFolder = "C:WalmartImport"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="tblmportTemp", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True, _
    Range:="Import!"

    strFile = Dir()
    Loop


    It works beautifully except in instances when a workbook being imported doesn't have a sheet called "Import". A runtime error is given and none of th workbooks are imported. The goal is to have all workbooks that have the "import" sheet import in and to stop the workbooks without the "import" sheet. Presently the user has to open all the workbooks to find the culprit, which can be a pain when entering 200+ workbooks.

    Is it possible to have the code only stop the import on workbooks without the "Import" sheet? If this is possible, is it possible to take it one step further and alert the user to the file name that wasn't imported or the number of files that weren't imported?

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error stops imort (2002)

    You can use an error handler to trap the error that occurs when the worksheet doesn't exist - error 3125 (I found this by reporting the error number):

    Sub Something()
    Dim strFolder As String
    Dim strFile As String

    On Error GoTo ErrHandler

    strFolder = "C:WalmartImport"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="tblmportTemp", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True, _
    Range:="Import!"
    strFile = Dir
    Loop
    Exit Sub

    ErrHandler:
    If Err = 3125 Then
    ' Worksheet not found
    MsgBox "The file " & strFile & " doesn't contain a worksheet named Import.", vbInformation
    ' And continue
    Resume Next
    Else
    ' Report error
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error stops imort (2002)

    Hans! That is beautiful!!! Thank you so much. The fact that it allows the correct workbooks to import is fabulous. But, the fact that it reporst the workbooks that don't import is so wonderful!

    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •