PDA

View Full Version : [SOLVED] Pulling specific lines from a text file into Excel



qitjch
02-05-2016, 09:53 AM
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 :D

GL_Balances worksheet:
15355

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

snb
02-06-2016, 10:07 AM
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