Results 1 to 4 of 4
  1. #1
    Wasp1's Avatar
    Wasp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    21

    If Statment Issues

    I have a folder on our company share drive that contains text files ONLY. I want to use this If Statement to exit Sub Procedure if files are other then Text Document. The issue is that objFolder.Type is pointing to the "File Folder".......



    'Check if the folder contains only Text Document If objFolder.Type <> "Text Document" Then
    MsgBox "Please remove files that are not Text Documents, then try again...", vbExclamation

    Exit Sub

    Else






    Option Compare Database
    Option Explicit
    Public FSO As New FileSystemObject

    Sub MyList()
    'Declaring variables
    Dim objFolder As Folder
    Dim objFile As File


    Dim strPath As String
    Dim db As Database
    Dim sfilename As String
    Dim ifilesize As String
    Dim SSQL As String
    Dim dmoddate As Date
    Dim fs, f, retstring
    Dim FileType As String
    Dim iRecCt As Long
    Dim DateLoaded As Date
    'Specify the path of the folder
    Set fs = CreateObject("scripting.filesystemobject")

    strPath = "C:\Documents\Darlene\"
    'Create the object of this folder
    Set objFolder = fs.GetFolder(strPath)
    'Check if the folder is empty or not
    If objFolder.Files.Count = 0 Then
    MsgBox "No files were found, please load your text files and try again...", vbExclamation

    Exit Sub
    End If


    'Check if the folder contains only Text Document
    If objFolder.Type <> "Text Document" Then
    MsgBox "Please remove files that are not Text Documents, then try again...", vbExclamation

    Exit Sub

    Else

    Set db = CurrentDb
    For Each objFile In objFolder.Files
    sfilename = objFile.Name
    ifilesize = objFile.Size
    dmoddate = objFile.DateLastModified
    FileType = objFile.Type
    DateLoaded = objFile.DateCreated



    Set f = fs.OpenTextFile(strPath & objFile.Name, ForReading, TristateFalse)



    iRecCt = 0
    Do While f.AtEndOfStream <> True
    iRecCt = iRecCt + 1
    retstring = f.ReadLine
    Loop
    f.Close





    'New
    SSQL = "INSERT INTO tblData (FileName, FileSize, FileModified, FileRecords, FileType, DateLoaded) VALUES ('" & sfilename & "', " & ifilesize & ", #" & dmoddate & "#," & iRecCt & ", '" & FileType & "', #" & DateLoaded & "#)"
    '==========================
    db.Execute (SSQL), dbFailOnError 'Change to 'DoCmd.RunSQL SSQL
    '===============================================




    Next objFile
    End If
    'End If

    Error_Handler_Exit:
    On Error Resume Next

    Set fs = Nothing
    Set db = Nothing

    MsgBox "Files are loaded to the tables."

    Exit Sub

    Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
    "Error Number: " & Err.Number & vbCrLf & _
    "Error Source: MyList" & vbCrLf & _
    "Error Description: " & Err.Description, _
    vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
    End Sub

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You don't need to remove the files or stop the code if it encounters a non-text file. You can just skip it.

    Something like:

    Code:
    For Each objFile In objFolder.Files
        sfilename = objFile.Name
        ifilesize = objFile.Size
        dmoddate = objFile.DateLastModified
        FileType = objFile.Type
        DateLoaded = objFile.DateCreated
    
        if filetype = "Text Document" 
            'Do whatever you're going to do with text documents here
        endif
    
    Next objfile

  3. #3
    Wasp1's Avatar
    Wasp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    21
    Thanks for replying back.....The code has to exit sub because we don't want any other files to get picked up. This forces my users to clean the folder......

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    anything your users are doing to 'clean' the folder you can likely do with filesystemobject commands, for instance move any non txt files to a specific folder. Otherwise you're going to have to cycle through all the files in the folder twice. Once to determine if there are any text files in the folder and cancel the operation if there are, the second time you'll be processing them to get line counts. It makes no sense unless you have a very good reason for doing it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. if statment
    By ismailkhannasar in forum Access
    Replies: 3
    Last Post: 01-31-2013, 06:48 AM
  2. if statment or case statment?
    By whojstall11 in forum Forms
    Replies: 4
    Last Post: 07-09-2012, 01:44 PM
  3. Like statment
    By brew in forum Programming
    Replies: 2
    Last Post: 12-01-2011, 03:23 AM
  4. IF statment criteria??
    By cthai in forum Queries
    Replies: 1
    Last Post: 03-19-2011, 02:51 AM
  5. SQL statment structure
    By oss_ma in forum Programming
    Replies: 1
    Last Post: 05-13-2007, 02:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums