Pulling specific lines from a text file into Excel

[qitjch]

Hello,

So currently, I have 15 text files that I receive via email everyday which are then automatically saved to a shared directory. The names of these textfiles. In my excel report, I need to use these textfiles to find the beginning and ending balances of certain GL accounts.

To accomplish this, I have two functions that feed into the macro. (all posted below).

The macro loops through the directory these text files are in and inputs the filename and filepath into Excel. I then use a combination of the two functions to determine which GL account is found within each text file by searching for an account string. If found, the function reports true. The 2nd function then pulls the last line of the text file which is the Ending Balance. Once I have the ending balance line and which GL account it belongs to, I use concatenation formulas to pull just the $ amount from the line.

I need to modify this code to also pull the Beginning Balance line from the text file.

I've included an example of one of the text files, as well as a copy of the GL_Balances sheet that gets created to run this macro. Please let me know if you have any questions or need any clarification on what I am trying to do.

Sub Gl_find_name_and_balances() 'finds all filenames and paths for the GL text files in the shared folder, daily ppv reporting and lists them on a new worksheet

'declare variables
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim fpath As String
Dim ts As Worksheet

'set file path to network drive
fpath = "\\SVUSINDFILE1\IndyManufacturing\Finance\Daily PPV Reporting\Email Attachments\GL Detail"  'change path if folder moves

'create new sheet in activework book named GL_Balances
ActiveWorkbook.Sheets.Add.Name = "GL_Balances" 'adds blank worksheet to the workbook

'enter headers into row 1
    Range("A1").Value = "File Name"
    Range("b1").Value = "File Path"
    Range("c1").Value = "001.18.4921"
    Range("d1").Value = "001.18.4923"
    Range("e1").Value = "001.18.4927"
    Range("f1").Value = "Ending Balance"
    Range("g1").Value = "Length"
    Range("h1").Value = "Find :"
    Range("i1").Value = "Trim1"
    Range("j1").Value = "Find Sp"
    Range("k1").Value = "Account"
    Range("l1").Value = "Left"
    Range("m1").Value = "Debit/(Credit)"

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object
Set objFolder = objFSO.GetFolder(fpath)

i = 1
'loops through each file in the directory and prints their files names, file path, whether it contains a specific account string, and last its Ending Balance line

For Each objFile In objFolder.Files
    'print file name
    Cells(i + 1, 1) = objFile.Name
    'print file path
    Cells(i + 1, 2) = objFile.path
    'print if 4921 gl string exists in file, True or False
    Cells(i + 1, 3) = StringExistsInFile("001.18.4921", objFile.path)
    'print if 4923 gl string exists in file, True or False
    Cells(i + 1, 4) = StringExistsInFile("001.18.4923", objFile.path)
    'print if 4927 gl string exists in file, True or False
    Cells(i + 1, 5) = StringExistsInFile("001.18.4927", objFile.path)
    'print Ending Balance Function
    Cells(i + 1, 6) = find_glending_balances(objFile.path)
    i = i + 1
Next objFile


'Add text to new sheet to extract GL ending balances into Column M

'declare variables
Dim lastrow As Long
Dim char1 As String, char2 As String, char3 As String

'define variables
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
char1 = ":"
char2 = " "
char3 = "D"

'text functions to parse ending balance dollars out of Ending Balance line
Range("g2").Formula = "=LEN(F2)"
Range("H2").Formula = "=FIND(" & Chr(34) & char1 & Chr(34) & ",F2)+1"
Range("I2").Formula = "=TRIM(MID(F2,H2,G2-H2))"
Range("J2").Formula = "=FIND(" & Chr(34) & char2 & Chr(34) & ",I2,1)"
Range("K2").Formula = "=IF(C2=TRUE,RIGHT($C$1,4),IF(D2=TRUE,RIGHT($D$1,4),IF(E2=TRUE,RIGHT($E$1,4)," & Chr(34) & Chr(34) & ")))"
Range("L2").Formula = "=TRIM(LEFT(I2,J2))"
Range("M2").Formula = "=IF(RIGHT(I2,1)=" & Chr(34) & char3 & Chr(34) & ",L2,L2*-1)"

'copies formulas in G2:M2 down to the last row containing info
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & lastrow)
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & lastrow)
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & lastrow)
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J" & lastrow)
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & lastrow)
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lastrow)
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M" & lastrow)


'converts column K to number for vlookup
Range("K:K").Select 'specify the range which suits your purpose
        With Selection
            Selection.NumberFormat = "General"
            .Value = .Value
        End With


'sets values in p2, q2, r2
Range("P2").Value = "4921"
Range("Q2").Value = "4923"
Range("R2").Value = "4927"

'adds vlookup formulas to next row
Range("p3").Formula = "=vlookup($p$2,k:m,3,0)"
Range("q3").Formula = "=vlookup($q$2,k:m,3,0)"
Range("r3").Formula = "=vlookup($r$2,k:m,3,0)"

'Add GL ending Balance to proper cell on recap by date
Sheets("PPV Recap by Date").Select
Range("C34").Value = Sheets("GL_Balances").Range("P3").Value
Range("d34").Value = Sheets("GL_Balances").Range("q3").Value
Range("e34").Value = Sheets("GL_Balances").Range("r3").Value

'turn off alerts and delete added tab
Application.DisplayAlerts = False
'Sheets("GL_Balances").Delete

'turn alerts back on
Application.DisplayAlerts = True
      
End Sub


'StringExistsInfile("string to search","filepath")
Public Function StringExistsInFile(ByVal theString As String, ByVal thefile As String) As Boolean

Dim L As Long, s As String, FileNum As Integer

FileNum = FreeFile

  Open thefile For Binary Access Read Shared As #FileNum
  L = LOF(FileNum)
  s = Space$(L)
  Get #1, , s
    Close #FileNum
  If InStr(1, s, theString) Then
    StringExistsInFile = True
  End If

End Function
 
'function finds the ending balance by finding the last string in the given text file
Function find_glending_balances(ByVal filepath As String) As String 

'declare variables
Dim myline As String
Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(filepath, ForReading)

Do Until objFile.AtEndOfStream
    strNextLine = objFile.ReadLine
    If Len(strNextLine) > 0 Then
        strLine = strNextLine
    End If
Loop
objFile.Close
'sets function = to last line found in file
find_glending_balances = strLine
End Function

Sorry for the sloppy code, but I'm still an ultra noob at this

GL_Balances worksheet:
gl_balances.xlsx

Text File:
https://drive.google.com/file/d/0B7o...ew?usp=sharing

[snb]

This should be sufficient:

Sub M_snb()
  c00 = "\\SVUSINDFILE1\IndyManufacturing\Finance\Daily PPV Reporting\Email Attachments\GL Detail"
  sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & c00 & "\*.txt"" /b/s /a-d").stdout.readall, vbCrLf)
     
  With ActiveWorkbook.Sheets.Add
    .Name = "GL_Balances_snb"
    .Cells(1).Resize(, 7) = Split("File Name|File Path|001.18.4921|001.18.4923|001.18.4927|Begin Balance|End Balance", "|")
     
    For j = 0 To UBound(sn) - 1
      sp = Filter(Split(Join(Filter(Split(CreateObject("scripting.filesystemobject").opentextfile(sn(j)).readall, vbCrLf), " Balance: "), ":"), ":"), "B", 0)
      .Cells(2 + j).Resize(, 7) = Array(Dir(sn(j)), sn(j), InStr(sn(j), "001.18.4921"), InStr(sn(j), "001.18.4923"), InStr(sn(j), "001.18.4927"), IIf(InStr(sp(0), "D"), "- ", "") & Split(Trim(sp(0)))(0), IIf(InStr(sp(1), "D"), "- ", "") & Split(Trim(sp(1)))(0))
    Next
  End With
End Sub