Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA File System Object Error - Line 52

Posted on 2014-04-18
2
Medium Priority
?
576 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

610 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