Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Unanswered: error "no files found"

    I am receiving No file found. but i know files are in the directory.


    Private Sub cmdImport_Click()
    Dim MyFileSpec, MyPath, fs As String
    Dim MyFileName As Variant
    Dim FileCounter As Integer
    Dim ActiveFile As String

    MyPath = "L:\Project\"
    MyFileSpec = "trans*.xls"

    MyFileName = GetFileList(MyPath & MyFile)

    If IsArray(LoadDirFileList) = False Then
    MsgBox "No files found"
    Exit Sub
    Else
    For FileCounter = LBound(LoadDirFileList) To UBound(LoadDirFileList)
    ActiveFile = MyFileName(FileCounter)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "master", MyName, False
    MsgBox "import complete"
    Next FileCounter
    End If
    end sub

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Do you get this error by the statement
    MyFileName = GetFileList(MyPath & MyFile)?

    How is the function GetFileList() written?

  3. #3
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74
    I fixed that (thanks) but I am receiving error on this line..

    Run-time error '3011':

    the microsoft jet database engine could not find the object 'Y:\WVT\MyFileName.xls'. make sure the object exists and that you spell its name and the path correctly.



    I am not even in that directory. not even my database in the drive. what is going on ??



    Private Sub cmdImport_Click()
    Dim MyFileSpec As String
    Dim MyPath As String
    Dim MyFileName As Variant
    Dim ActiveFile As String
    Dim FileCounter As Integer

    MyPath = "L:\Project\1\"
    MyFileSpec = "trans*.xls"
    MyFileName = GetFileList(MyPath & MyFileSpec)

    If IsArray(MyFileName) = False Then
    MsgBox "No files found"
    Exit Sub
    Else
    For FileCounter = LBound(MyFileName) To UBound(MyFileName)
    ActiveFile = MyFileName(FileCounter)
    DoCmd.TransferSpreadsheet acImport, 8, "master", "MyFileName", 0
    MsgBox "import complete"
    Next FileCounter
    End If
    End Sub

  4. #4
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74
    Here is the GetFileList

    Function GetFileList(FileSpec As String) As Variant

    ' Returns an array of filenames that match FileSpec
    ' If no matching files are found, it returns False

    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String

    On Error GoTo NoFilesFound

    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound

    ' Loop until no more matching files are found
    Do While FileName <> ""
    FileCount = FileCount + 1
    ReDim Preserve FileArray(1 To FileCount)
    FileArray(FileCount) = FileName
    FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function

    NoFilesFound:
    GetFileList = False
    End Function

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try ActiveFile (unquoted) instead of "MyFileName"

    For FileCounter = LBound(MyFileName) To UBound(MyFileName)
    ActiveFile = MyFileName(FileCounter)
    DoCmd.TransferSpreadsheet acImport, 8, "master", "MyFileName", 0
    MsgBox "import complete"
    Next FileCounter

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74
    thanks for helping me. really appreciated. I will try the code when i get to my office next week.

Posting Permissions

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