Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Unanswered: VBA RunSavedImportExport Error Help Please

    Hi I could do with some advice please

    I am running a procedure to loop through a directory and import xls files into a specific table, using the “DoCmd.RunSavedImportExport function” in conjunction with a import specification, but I am getting this error, and I am stumped.

    “ Run time error 31602 The specification with the specified does not exist, specify a different index 0"

    Thanks for your help in advance

    Code:
    Public Function Import_From_Excel()
     
     ' This subprocedure determines whether
     ' any Excel files exist in the folder stored
     ' in the strcPath constant; if there are,
     ' this subprocedure imports the data in the
     ' Excel files and then moves the files to
     ' the folder stored in the strcNewPath constant.
     ' Store the name of the table into which
     ' the data will be imported
     
     Const strcTableName As String = "Tbl_BranchReply"
     
     Dim strPath As String
     Dim strPath1 As String
     Dim strNewPath As String
     Dim strFile As String
     Dim strFileList() As String
     Dim intFile As Integer
     Dim strFullPath As String
     Dim strFullNewPath As String
     Dim strcPath As String
     Dim AddPath As String
     Dim AddPath1 As String
     strPath1 = Application.CurrentProject.Path
     AddPath = "\BranchReply\"
     AddPath1 = "\BranchFilesImported\"
     AddPath2 = "\BranchImportTemp\"
     
     strcPath = strPath1 & AddPath2
     strcNewPath = strPath1 & AddPath1
     
     DoCmd.SetWarnings False
     
     ' See if path constant ends in a backslash:
     If Right(strcPath, 1) = "\" Then
      strPath = strcPath
     Else
      strPath = strcPath & "\"
     End If
     
      ' See if new path constant ends in a backslash:
     If Right(strcNewPath, 1) = "\" Then
      strNewPath = strcNewPath
     Else
      strNewPath = strcNewPath & "\"
     End If
      
     ' Loop through the Excel files in the folder
     ' (if any) and build file list:
     strFile = dir(strPath & "*.xls")
     While strFile <> ""
      intFile = intFile + 1
      ReDim Preserve strFileList(1 To intFile)
      strFileList(intFile) = strFile
      strFile = dir()
     Wend
       
     ' See if any files were found:
     If intFile = 0 Then
     
      MsgBox strcPath & vbNewLine & vbNewLine _
       & "The above directory contains no Excel " _
       & "files.", _
       vbExclamation + vbOKOnly, "Program Finished"
       
      GoTo Exit_Import_From_Excel
      
     End If
      
     ' Loop through the list of files:
     For intFile = 1 To UBound(strFileList)
      
      ' Initialise paths:
      strFullPath = strPath & strFileList(intFile)
      strFullNewPath = strNewPath & strFileList(intFile)
      
      ' Import into Access:
      
       DoCmd.RunSavedImportExport Import - BranchOpenOrders
      
       
      ' Copy file to new location:
      FileCopy strFullPath, strFullNewPath
      
      ' Delete old file:
      Kill strFullPath
      
     Next
     
     MsgBox UBound(strFileList) & " file(s) were imported", _
      vbOKOnly + vbInformation, "Program Finished"
     
    Exit_Import_From_Excel:
    
     Exit Function
     
     DoCmd.SetWarnings True

  2. #2
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Solved

    Hi found the error its in the code I needed to wrap the Specification Name in Brackets & Quotes

    Code:
     ' Import into Access:
      
       DoCmd.RunSavedImportExport ("Import - BranchOpenOrders")

Posting Permissions

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