Custom Import VBA issue

Question

I use a custom made import function (skeleton below) to gather data from "Linked" text files (containing header/records/footer). The first field of the header will always contains 123, but I need to skip those records where the second field in the header begins with the letters "AC".

I tried to create a loop that, when it finds first field contains "123" and the second field starts with "AC" then to skip the records until it finds another "123", then assess again through the loop and only break out of the loop to write the records that don't contain "AC" in the second field of the header.

However I get "Compile error: Augment not optional" based on line Loop Until rs!Field1 = "123" And Left(rs!Field2) <> "AC", when trying the following and not quite sure how else to go about checking if the string in the second field starts with "AC". Thanks.

Public Function FormatTextFile()

Dim db As Database
Dim rs, rsa As Recordset
Dim cCount as double

Set db = CurrentDb
Set rs = db.OpenRecordset("Flow_20160316")

cCount = 1

Do

    Do While rs!Field1 = "123" And Left(rs!Field2, 2) = "AC"
        Debug.Print "Code Skipped on Record " & cCount
        cCount = cCOunt + 1
        rs.MoveNext
    Loop Until rs!Field1 = "123" And Left(rs!Field2) <> "AC"

Select Case rs!Field1

    Case Is = "123"
        'Code continues and writes some variables to tables'
        Case else
        Debug.Print "Code Skipped on Record " & cCount
    End select
    cCount = cCOunt + 1
rs.MoveNext

Loop until rs.eof

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

This line *does* compile. I think you have simplified your code too much. [mcve] ==> Verifiable — Andre, Mar 18 '16 at 10:45
BTW, `Case Is = "123"` is a **very** unorthodox way to write `Case "123"` :) — Andre, Mar 18 '16 at 10:48
Apologies, that was the line that previously wouldn't compile. I have amended my original to show the line that doesn't compile to the `Loop Until` section. Thanks for the comments on the `Case is = ""` section. I will be looking into that shortly =) — Wowdude, Mar 18 '16 at 11:06
I also amended the main paragraph to show that the data is being pulled form a "Linked" text file. Not sure if this makes a difference. — Wowdude, Mar 18 '16 at 11:28

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Do / While / Until loops in VBA come in a variety of forms, there is an answer here on SO (but I can't find it now) that lists 7 versions.

(Edit: found it - Warning: don't go there if you don't have some spare time to waste)

But

Do While <condition>
    ' stuff
Loop Until <condition>

isn't allowed - it's a syntax error. The condition can appear only once.

Here is a decent overview: http://www.excelfunctions.net/VBA-Loops.html#DoWhileLoop

With that being said, your code with the nested loops seems overly complicated. Can't you simply do this?

Do While Not rs.EOF

    If rs!Field1 = "123" And Left(rs!Field2, 2) = "AC" Then
        Debug.Print "Code Skipped on Record " & cCount
    Else
        ' regular code
    End If

    cCount = cCount + 1
    rs.MoveNext

Loop

Note: by using Do While Not rs.EOF you avoid an error if rs is empty.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 18 '16 at 11:29

Andre

26,751
7
36
80

I see. Of course, you can only use the condition at the start of the loop or at the end, and I have used it at both. That makes sense. So instead of starting with a `Do While` and looping with a `Loop Until`, i should just need a `Do While` and loop with a simple `Loop`? – Wowdude Mar 18 '16 at 11:37
That is perfect Andre! I love Loops and nested Loops but unfortunately i'm not very good with them and struggle building them with efficiency whilst maintaining simplicity. But yours make much more sense. Thank you for your advice. Win for you! – Wowdude Mar 18 '16 at 11:47

Custom Import VBA issue

1 Answers1