VBA File Parsing

[mdinge01]

I created a script that parses a text file. there is a Stop @ a Do while loop . If I run the module that parses the module it runs fine. I then select another file and it errors out at the Stop. If I break the code it will run the parsing of the next file. Any idea why this happens and what I can do to correct.

Regards

 

[Joe4]

Welcome to the Board!

Any idea why this happens and what I can do to correct.

Not without seeing your actual code.
See here: How to Post Your VBA Code

 

[mdinge01]

Not without seeing your actual code.
See here: How to Post Your VBA Code

Here is the code:

'Sub Alero_subRunner()
'processOneFile ThisWorkbook.path & Application.PathSeparator & "LD_PL_June_2023_Simple.TXT"
'End Sub

Sub processOneFile(path)
Worksheets("Costs").Activate
'Sub processOneFile()
Dim theFile As String
Dim oneLine As String
Dim year As String
Dim Mth As String
Dim Position As Integer
Dim YearMonth As String
Dim intOk As Integer
Dim strAccountNum As String
Dim strAccountDesc As String
Dim strAccountType As String
Dim intCurrentMth As Integer
Dim intCurrentMthPrev As Integer
Dim row As Long
row = Cells(Rows.Count, 1).End(xlUp).row + 1
On Error Resume Next
Open path For Input As #1
'theFile = Input(LOF(1), #1)
Line Input #1, oneLine
Line Input #1, oneLine
Position = InStr(1, oneLine, "/")
year = Mid(oneLine, Position + 1, 4)
Mth = Mid(oneLine, Position - 2, 2)
YearMonth = Mth & "-" & year

Do Until Mid(oneLine, 3, 7) = "REVENUE"
Line Input #1, oneLine

If EOF(1) Then Stop
On Error Resume Next
DoEvents
Loop


Line Input #1, oneLine
Line Input #1, oneLine
Do Until Left(oneLine, 2) = " -"

If Mid(oneLine, 4, 1) = "4" Then
strAccountType = "Revenue"
Else
strAccountType = "Expense"
End If

strAccountNum = Mid(oneLine, 2, 10)
strAccountName = Mid(oneLine, 12, 35)
intCurrentMthRev = Mid(oneLine, 48, 17)
intCurrentMthYTD = Mid(oneLine, 69, 17)
intCurrentYearMth = Mid(oneLine, 90, 17)
intPreviousYTD = Mid(oneLine, 111, 17)


Dim x As Long
Debug.Print oneLine

If EOF(1) Then Exit Sub
For x = 1 To 1
Cells(row, 1).Value = strAccountType
Cells(row, 2).Value = YearMonth
Cells(row, 3).Value = strAccountNum
Cells(row, 4).Value = strAccountName
Cells(row, 5).Value = intCurrentMthRev
Cells(row, 6).Value = intCurrentMthYTD
Cells(row, 7).Value = intCurrentYearMth
Cells(row, 8).Value = intPreviousYTD
row = row + 1
Line Input #1, oneLine
' On Error Resume Next
Next
DoEvents
Loop
'End If

Stop
Close #1
MsgBox "File has Been Loaded"
End Sub

 

[Joe4]

I would recommend stepping through your code line-by-line and watch to see what is going on. Many times, the issues become evident then.

However, I see two things I would definitely change.

You should NEVER use reserved words (words used by Excel/VBA already for names of functions, properties, methods, etc) as variable names.
You have used ROW and YEAR as variable names. Doing that can cause unexpected results and errors.

You are actually trying to use BOTH (your "row" variable and the "Row" property/method) here is this line:

row = Cells(Rows.Count, 1).End(xlUp).row + 1

The ".row" should be capitalized to show ".Row", but VBA is confused and thinks you are referencing your variable here.

So you should change these two variables.
To avoid any conflicts, I often preface my variables with the word "my", i.e. "myRow" and "myYear".