PDA

View Full Version : List text files in folder based on partial file name - repeats first file found



bifjamod
03-25-2016, 03:01 PM
I am trying to create a text file list of the files in a folder which contain (start with) a partial file name. For example, the files in question would all be named ACH-PMS-PRELIM-MMDDYY-nnnnnnnnnn.txt. I need to list all the files where MMDDYY matches a specific date. In the script I have so far, it continuously writes the first file found to the text file, resulting in the necessity of killing Excel. If I move the entire write function part of the script (from Dim oType2Stream to into the loop, it only writes the first file found once, then stops.

Can someone please point me in the write direction (see what I did there?) for this?


Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\OneDrive\FGLOPS\ACERESULTS\ACH Reports\ACH Text Output\PMS Statements\"
MyFile = Dir(MyFolder & "ACH-PMS-PRELIM-021616-*.txt")

Dim oType2Stream As TextStream
Dim oFS As New Scripting.FileSystemObject
strType2Path = "C:\Dropbox\Desktop Work File\ACH Type 2 File Report.txt"
'Open file for writing, create if it doesn't exist
Set oType2Stream = oFS.OpenTextFile(strType2Path, ForWriting, True)
oType2Stream.WriteLine "The following files ...."

Do While MyFile <> ""
oType2Stream.WriteLine MyFile
Loop

oType2Stream.Close

Admittedly, I am mashing together parts of two different scripts, so maybe part of the problem lies therein.

gmayor
03-26-2016, 03:54 AM
Presumably you want to write the list to Excel?

Option Explicit

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim NextRow As Long
MyFolder = "C:\OneDrive\FGLOPS\ACERESULTS\ACH Reports\ACH Text Output\PMS Statements\"
If ActiveSheet.Range("A1") = "" Then ActiveSheet.Range("A1") = "Filename"
MyFile = Dir$(MyFolder & "ACH-PMS-PRELIM-021616-*.txt")
While MyFile <> ""
With ActiveSheet
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & NextRow) = MyFile
End With
MyFile = Dir$()
DoEvents
Wend
lbl_Exit:
Exit Sub
End Sub

bifjamod
03-26-2016, 12:18 PM
Presumably you want to write the list to Excel?

Option Explicit

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim NextRow As Long
MyFolder = "C:\OneDrive\FGLOPS\ACERESULTS\ACH Reports\ACH Text Output\PMS Statements\"
If ActiveSheet.Range("A1") = "" Then ActiveSheet.Range("A1") = "Filename"
MyFile = Dir$(MyFolder & "ACH-PMS-PRELIM-021616-*.txt")
While MyFile <> ""
With ActiveSheet
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & NextRow) = MyFile
End With
MyFile = Dir$()
DoEvents
Wend
lbl_Exit:
Exit Sub
End Sub

Actually, I'm writing it to a text file, but it works! Thank you! I'm going to have to ponder this for a while to figure out the difference with your approach and mine, but yours works!

Here's my revised code:

Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\OneDrive\FGLOPS\ACERESULTS\ACH Reports\ACH Text Output\PMS Statements\"
MyFile = Dir(MyFolder & "ACH-PMS-PRELIM-031516-*.txt")

Dim oType2Stream As TextStream
Dim oFS As New Scripting.FileSystemObject
strType2Path = "C:\Dropbox\Desktop Work File\ACH Type 2 File Report.txt"
'Open file for writing, create if it doesn't exist
Set oType2Stream = oFS.OpenTextFile(strType2Path, ForWriting, True)
oType2Stream.WriteLine "The following files are Type 2 ACH payments; pay them via Quickbooks."

While MyFile <> ""
oType2Stream.WriteLine MyFile
MyFile = Dir$()
DoEvents
Wend
oType2Stream.Close
End Sub

gmayor
03-26-2016, 10:19 PM
To write to a text file

Option Explicit

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim NextRow As Long
Dim strType2Path As String
Dim oType2Stream As Object
Dim oFSO As Object

MyFolder = "C:\OneDrive\FGLOPS\ACERESULTS\ACH Reports\ACH Text Output\PMS Statements\"
strType2Path = "C:\Dropbox\Desktop Work File\ACH Type 2 File Report.txt"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oType2Stream = oFSO.OpenTextFile(strType2Path, ForAppending, True)
oType2Stream.WriteLine "The following files are Type 2 ACH payments; pay them via Quickbooks."
MyFile = Dir$(MyFolder & "ACH-PMS-PRELIM-021616-*.txt")
While MyFile <> ""
oType2Stream.WriteLine MyFile
MyFile = Dir$()
DoEvents
Wend
oType2Stream.Close
lbl_Exit:
Set oType2Stream = Nothing
Set oFSO = Nothing
Exit Sub
End Sub

bifjamod
03-28-2016, 08:29 AM
Thanks again; the script I mentioned above is successfully writing to text as well. I did a little digging to learn from your solution, and found that the key is the MyFile = Dir$() line in the While/Wend loop. Now, I am curious about some things in your solution:
1) Why the While/Wend loop, instead of the Do While/Loop variation that seems to be the more modern and preferred method of doing this type of operation? It works either way in my script.
2) Why the DoEvents instruction? I get what that is for; in my case, it may just be that the scope of the operation is so small as to make this unnecessary.
3) Why did you create "lbl_Exit:"? Is that just a personal coding preference for you? I think I see the benefit in it, but would like to know your rationale.

snb
03-28-2016, 09:09 AM
Sub M_snb()
CreateObject("wscript.shell").exec("cmd /c dir ""C:\OneDrive\FGLOPS\ACERESULTS\ACH Reports\ACH Text Output\PMS Statements\ACH-PMS-PRELIM-021616-*.txt""/b >C:\Dropbox\Desktop_Work_File\ACH_Type2_File_Report.txt")
End Sub

NB. If you avoid spaces in directory names & in filenames VBA life becomes much more comfortable.