?
Solved

VBA File System Object Error - Line 52

Posted on 2014-04-18
2
Medium Priority
?
571 Views
Last Modified: 2014-04-23
Hi,

I am upgrading an old access 2003 database to 2010 and am having some issues with using the file system object. My references are all set correctly to Office14.0.

Can anyone help with a solution to the error I am getting on line 52?

Sub ListFilesInFolder()

'** empty the temp table TT_FILE_LIST
'** add the files in the TT_FILE_LIST table with print status set to "not printed"
'** If the file has the timestamp PRTxxxx then put status "printed [xxxxxx]"

    Dim dbFailOnError
    Dim rs As New ADODB.Recordset
    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.AccessConnection
    Dim strSQL As String, i, iFileName
    
    Dim vAutoPrintDirectory, vAutoPrintFileExtension, totalFiles, strCurFile, vFileNameNoExt
    Dim vSearch, vSearchDate, vPrintingStatus, vPrintingStatusFlag
        
    DoCmd.SetWarnings False

    '** set the vGetFileProcessing to true (= processing)
    vGetFileProcessing = True
    
    'Get the printing
    strSQL = "SELECT MT_PRINTING_SETTINGS.AutoPrintDirectory, MT_HANDLER_SETTINGS.HandlerFileExtension FROM MT_PRINTING_SETTINGS INNER JOIN MT_HANDLER_SETTINGS ON MT_PRINTING_SETTINGS.AutoPrintHandlerID = MT_HANDLER_SETTINGS.HandlerID"
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        vAutoPrintDirectory = rs.Fields("AutoPrintDirectory")
        vAutoPrintFileExtension = rs.Fields("HandlerFileExtension")
    End If
    Set rs = Nothing
    
    'Make sure the folder setting is in the table
    If IsNull(vAutoPrintDirectory) = True Then
        Call showProcessStatus("UF_DASHBOARD", Chr(13) & Chr(10) & "Please select a folder where to look for files..", 1)
        Call hideProcessStatus("UF_DASHBOARD", 2)
        Exit Sub
    End If
    
    'Make sure the extension setting is in the table
    If IsNull(vAutoPrintFileExtension) = True Then
        Call showProcessStatus("UF_DASHBOARD", Chr(13) & Chr(10) & "Please select a file extension..", 1)
        Call hideProcessStatus("UF_DASHBOARD", 2)
        Exit Sub
    End If
    
    'Empty the TT_FILE_LIST table
    Call showProcessStatus("UF_DASHBOARD", "Collecting files from : " & vAutoPrintDirectory, 0)
    strSQL = "DELETE FROM TT_FILE_LIST"
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
    Set rs = Nothing
    
    'Loop through files in specific folder and add them to the TT_FILE_LIST table
    With Application.FileSearch 'ERRORS HERE
        .NewSearch
        .LookIn = vAutoPrintDirectory
        .SearchSubFolders = False
        .FileName = "*." & vAutoPrintFileExtension

        If .Execute() > 0 Then
            totalFiles = .FoundFiles.Count
            
            For i = 1 To totalFiles
                   
                'fnWait (1)
                
                strCurFile = .FoundFiles(i)
                iFileName = GetFilenameFromPath(strCurFile)
                
                Call showProcessStatus("UF_DASHBOARD", Chr(13) & Chr(10) & "File " & i & "/" & totalFiles & " : " & iFileName, 0)
                                
                DoEvents
                
                '*** CANCEL the search
                If vGetFileProcessing = False Then
                    Call EmptyFileList
                    Call PopulateFileList("FileNameListBox")
                    
                    'Update the value of the selected files
                    Forms![UF_DASHBOARD].Controls("SelectedFileCountText").Value = Forms![UF_DASHBOARD].Controls("FileNameListBox").ItemsSelected.Count
                    
                    'Update the value of the found files
                    Forms![UF_DASHBOARD].Controls("TotalFileCountText").Value = GetTotalFileInList
                    
                   'Update the value of the printed files
                    Forms![UF_DASHBOARD].Controls("PrintedFileCountText").Value = 0
                    
                    Call showProcessStatus("UF_DASHBOARD", Chr(13) & Chr(10) & "File collection aborted.", 1)
                    'Add the print file in the log table
                    Call AddDBLog("Cancel", "File search", "File search aborted")
                    Call hideProcessStatus("UF_DASHBOARD", 2)
                    
                    Exit Sub
                End If
                
                'Check file name for printed information
                vPrintingStatus = "Not printed"
                vPrintingStatusFlag = 0
                
                'If the filename (without extension) is longer than 12, capture the last 12 characters before the file extension
                vFileNameNoExt = Left(iFileName, Len(iFileName) - 4)
                
                If (Len(vFileNameNoExt) > 12) Then
                    vSearch = InStrRev(vFileNameNoExt, "_PRT", -1, vbTextCompare)
                    If vSearch = (Len(iFileName) - 15) Then
                        vSearchDate = Mid(vFileNameNoExt, vSearch + 4, (vSearch + 8))
                        vPrintingStatus = "Printed (" & Right(vSearchDate, 2) & "/" & Mid(vSearchDate, 5, 2) & "/" & Left(vSearchDate, 4) & ")"
                        vPrintingStatusFlag = 1
                    End If
                End If
                
                'Add the record in the table
                strSQL = "INSERT INTO TT_FILE_LIST ( FileName, FilePath, FilePrintStatus, FilePrintStatusFlag ) " & _
                "VALUES (" & Chr(34) & iFileName & Chr(34) & "," & Chr(34) & vAutoPrintDirectory & Chr(34) & "," & Chr(34) & vPrintingStatus & Chr(34) & "," & vPrintingStatusFlag & ")"
                rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
                Set rs = Nothing
            Next i
        Else
            Call showProcessStatus("UF_DASHBOARD", "No files were found!" & Chr(13) & Chr(10) & "Please make sure the path and file type are correct.", 1)
            
            'Closes the process status after 2 seconds
            Call hideProcessStatus("UF_DASHBOARD", 2)
        End If
        
        'Closes the process status after 1 second
        Call hideProcessStatus("UF_DASHBOARD", 1)
        
    End With
    
    'Update the value of the found files
    Forms![UF_DASHBOARD].Controls("TotalFileCountText").Value = GetTotalFileInList
    
    'Update the value of the printed files
    Forms![UF_DASHBOARD].Controls("PrintedFileCountText").Value = GetTotalFilePrinted
    
    Forms![UF_DASHBOARD].Controls("BtnSearchFiles").Caption = "Search for files"
    
    'Log
    Call AddDBLog("Success", "File search", GetTotalFileInList & " files found in " & vAutoPrintDirectory)
    
    vGetFileProcessing = False
    
End Sub

Open in new window

0
Comment
Question by:discogs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40010263
As far as I know this was removed in 2007. You can use the Dir function instead:

Dim sFile As String
'/ below would show only .xls files, for example
sFile = Dir("Path to your folder", ".xls")

Do While Len(sFile) > 0
  'do something here with sFile
  sFile = Dir
Loop

This will get all Files, but will not get anything in Subfolders. If you need to do that, you would need to call a function recursively and account for folder.

If you want to get all files, you do this:
sFile = Dir("Path to your folder", ".*")
0
 

Author Closing Comment

by:discogs
ID: 40019056
ta
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question