UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Access Vba To Find Text File In A Folder Using Dir(), Access 2016    
 
   
Olaf123
post May 28 2020, 03:09 PM
Post#1



Posts: 55
Joined: 9-June 16



Hello,
I am trying to update my code to fulfill a business need... In my scenario, I have a folder where some text files have been saved via auto sweep process. Here is the naming convention of those files: XXXXyyyymm. A new file is saved with the current year and a new month. However, it does not mean that every month I will have a new file. Some months there were/will be no new files ( Example: XXXX202001, XXXX202003, XXXX202004). I would like that my code will prompt me to manually input my month and have the code to check the folder if that file exists. The below listed code does some of those steps except giving me the correct answer. Currently, when I am putting my month 02 (Feb) , it returns the file found and gives me the wrong file name XXXX202001, but it should give me file is not found.
Can someone please help me to correct this error?


Function FileExists()
Dim folderpath As String
Dim filename As Variant
Dim file As String
Dim path As String

DoCmd.SetWarnings False

Filename = InputBox("Please put a number of the month you would like to find")

folderpath = "C:\2020\"

path = folderpath & "\*.txt"

filename = Dir(path)

Do While (filename <> "")
If InStr(filename, "2020") > 0 Then
MsgBox "found " & filename
Exit Function
End If
filename = file
Loop
If filename = file Then
MsgBox filename
Else
MsgBox "File does not exist."
End If
DoCmd.SetWarnings True

Exit Function
This post has been edited by Olaf123: May 28 2020, 03:15 PM
Go to the top of the page
 
theDBguy
post May 28 2020, 03:24 PM
Post#2


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. You declared a variable called "file," but I don't see where you assigned a value to it. So, I think you meant to do this:
CODE
filename = Dir()

instead of this:
CODE
filename = file

Hope that helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 28 2020, 03:34 PM
Post#3



Posts: 55
Joined: 9-June 16



Hello theDBguy,
Thank you for your reply. I tried your recommendation and unfortunately when I changed to this: filename = file , the message fox is appearing blank and I I can't tell whether this file exists or not.
Can you please give me more ideas?
Go to the top of the page
 
MadPiet
post May 28 2020, 03:35 PM
Post#4



Posts: 3,799
Joined: 27-February 09



?LEN(DIR("C:\Users\User\Documents\Bookdata2.accdb"))>0

You'd create a super simple function...

CODE
Public Function DoesFileExist(ByVal strFile as string)

  DoesFileExist = LEN(DIR(strFile))>0

end function


Then just call it...
If DoesFileExist("C:\Temp\test.tmp") Then
--
ELSE

END IF
Go to the top of the page
 
ADezii
post May 28 2020, 03:55 PM
Post#5



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. If you want to maintain your existing Format, or something close to it, you must be sure of two things:
    1. All Files in the C:\2020\ Folder MUST be in the Format of XXXX2020mm.txt.
    2. All Files in the C:\2020\ Folder are actually for the year 2020, you cannot have a File named XXXX201902.txt. You actually can, but will need more validation on the Coding.
  2. Let's assume for the sake of argument, that your C:\2020\ Folder is as defined in the Image below.
  3. The following Code will produce the desired results given the Value you provide to the InputBox() Function.
    CODE
    Dim folderpath As String
    Dim filename As String
    Dim strFilesInFolder As String
    Dim file As String
    Dim path As String

    filename = InputBox$("Please put a number of the month you would like to find")

    If filename = "" Or Not IsNumeric(filename) Or Len(filename) > 2 Then Exit Sub

    If Len(filename) <> 2 Then filename = Format$(filename, "00")   'Month must be mm

    folderpath = "C:\2020\"

    path = folderpath & "\*.txt"

    strFilesInFolder = Dir(path)

    Do While strFilesInFolder <> ""
      If Mid$(strFilesInFolder, 9, 2) = filename Then
        MsgBox "found " & filename
      End If
      strFilesInFolder = Dir
    Loop
  4. For the 2nd Image, I Inputted a Value of either 2 or 02.
  5. For the 3rd Image, I inputted a Value of 12.
  6. Any questions, feel free to ask.

Attached File(s)
Attached File  2020.JPG ( 20.48K )Number of downloads: 0
Attached File  02.JPG ( 18.08K )Number of downloads: 0
Attached File  12.JPG ( 17.09K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post May 28 2020, 06:00 PM
Post#6


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (Olaf123)
Hello theDBguy,
Thank you for your reply. I tried your recommendation and unfortunately when I changed to this: filename = file , the message fox is appearing blank and I I can't tell whether this file exists or not.
Can you please give me more ideas?

Hi. Just to make sure you didn't misunderstand what I said, can you please post your modified code?

You already had this: filename = file, so you couldn't have changed your code to that. What I said was to use this instead: filename = Dir()

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 28 2020, 06:13 PM
Post#7



Posts: 55
Joined: 9-June 16



Thank you everyone for trying to help me.
I am getting closer to resolve this issue. The code runs, but it has a small issue. When I input for example the most recent existing month scenario 1- 04, first message appears and says, file does not exist, I click Ok, than message appears stating that the file was found and gives me the correct file name. If I will put 02, it correctly states that this file is missing. If I will input another (third) scenario - 03, It gives me both file 04 and 03. Why in first case it gives me 2 answers (first incorrect, second correct) and in scenario 2 it is all good? Also, why in third scenario it gives me both files when I click Ok 2 times, but I am looking only for one answer? Can you please help?

My current code is as follows:

Function FileExists()
Dim UserInput As String
Dim filename As String
Dim filepath As String
Dim FileSpec As String

DoCmd.SetWarnings False

UserInput = InputBox("Please put a number of the month you would like to find")

filepath = ("C:\2020\")
FileSpec = "*.txt"

filename = Dir(filepath & FileSpec)

Do While (filename <> "")


If InStr(filename, "2020") > 0 And InStr(filename, UserInput) > 0 Then

MsgBox "found " & filename

Else

MsgBox "File does not exist."

End If
filename = Dir
Loop

DoCmd.SetWarnings True

Exit Function
End Function
This post has been edited by Olaf123: May 28 2020, 06:25 PM
Go to the top of the page
 
theDBguy
post May 28 2020, 06:44 PM
Post#8


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just to make sure we're all on the same page with you, could you please post screenshots of all the message boxes you get for each user input? Thanks!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 29 2020, 12:05 PM
Post#9



Posts: 55
Joined: 9-June 16



Hello,
Here is the screen shots for scenarios#1, 2 and 3
This post has been edited by Olaf123: May 29 2020, 12:17 PM
Attached File(s)
Attached File  Scenario_1.doc ( 68K )Number of downloads: 2
Attached File  Scenario_1.doc ( 68K )Number of downloads: 1
Attached File  Scenario_2.doc ( 58K )Number of downloads: 2
Attached File  Scenario_3.doc ( 60K )Number of downloads: 2
 
Go to the top of the page
 
theDBguy
post May 29 2020, 12:30 PM
Post#10


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks! In the last scenario where you input "03" but got a message for finding "???202004.txt," what were the "???" (the part you covered up) say? Is it a bunch of numbers too? If so, was there a "03" in it?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 29 2020, 12:49 PM
Post#11



Posts: 55
Joined: 9-June 16



The part that I covered is the actual name of the data file that I renamed to XXXX because I can't put that name on the public forum. 03 is going to be at the end of the file name, just like this: XXXX202003.txt. Since I do not have the file with March data, the code will tell me know that the file does not exist.
Thank you for looking into this.
This post has been edited by Olaf123: May 29 2020, 12:58 PM
Go to the top of the page
 
theDBguy
post May 29 2020, 01:03 PM
Post#12


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. I understand hiding the actual data from us, but that didn't answer my question. Did the "data" also contain "03" in it? Your code is not looking for "03" at the end of the filename. It's looking for "03" anywhere in the filename. So, if the filename was, for example "XX03X202004.txt," your code will still show you a message box that it found "03." If you want to only check the end of the filename, you could try using the Right() function.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 29 2020, 01:20 PM
Post#13



Posts: 55
Joined: 9-June 16



The file name by it self does not have 03 in the beginning, however if the code is looking at the whole path there is "03" in the name of the network where these files will be located. I tried to add this code to the

If InStr(Right(filename, Len(filename) - InStrRev(filename, "\")), "2020") > 0 And InStr(filename, UserInput) > 0 Then

but unfortunately this give me the same result. If I do Right() or Mid(), the code does not find 04 input

Any other thoughts that you can share with me regarding this issue?
Thanks.
Go to the top of the page
 
theDBguy
post May 29 2020, 01:45 PM
Post#14


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Okay, let's assume the filename will always be in this format "YYYYMM.txt" If so, we could try it this way:

If InStr(Right(filename,10),"2020" & UserInput)>0 Then

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 29 2020, 02:03 PM
Post#15



Posts: 55
Joined: 9-June 16



Thank you theDBguy. We are getting even more close.
when I input 04, it gives me first "Not found, I click Ok, the code finds the file with 04 at the end, when I input 03, the code gives me correctly "not found", I click OK, it gives me "not found" again. when I input 02, it gives me found 02 . Then I click Ok and it gives me "not found".
I do look at 2 thinks in a matter to give me 1 answer, but for some reason I get 2 answers still.
I just need the code to go into the folder look for the file that has inputted extension and tell me if the file with that extension exists or not.
This post has been edited by Olaf123: May 29 2020, 02:04 PM
Go to the top of the page
 
theDBguy
post May 29 2020, 02:13 PM
Post#16


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Perhaps, you need to swicth to using FSO, so you can use the FileExists() method.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 29 2020, 02:23 PM
Post#17



Posts: 55
Joined: 9-June 16



I am not familiar with this option. Would you be able to help me to adapt what I have to this method?
Go to the top of the page
 
theDBguy
post May 29 2020, 03:08 PM
Post#18


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Sure, but let's try it one more time using Dir(). For example:

CODE
filename = Dir(folderpath & "\2020" & UserInput & ".txt")
If filename <> "" Then
    MsgBox "Found " & filename & "!"
Else
    MsgBox "Not found " & filename & "!"
End If

Would that work?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Olaf123
post May 29 2020, 03:18 PM
Post#19



Posts: 55
Joined: 9-June 16



I tried your code and unfortunately it returned "Not found" to all my inputs. :-(
Go to the top of the page
 
MadPiet
post May 29 2020, 04:13 PM
Post#20



Posts: 3,799
Joined: 27-February 09



I modified someone's code a tiny bit... why isn't this FileExists thing just a function... it can return one of two values: True (file exists) or False (doesn't exist).

CODE
Public Function FileExists3(ByVal FolderPath As String, ByVal UserInput As String, ByVal FileExt As String) As Boolean
    Dim filename As String

    filename = Dir(FolderPath & "\" & UserInput & "*." & FileExt)
    If filename <> "" Then
        'MsgBox "Found " & filename & "!"
        FileExists3 = True
    Else
        'MsgBox "Not found " & filename & "!"
        FileExists3 = False
    End If
End Function


Then you'd just call it like this:

If FileExists3("C:\SomeFolder","2020","XLSX") Then...
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2020 - 09:26 AM