Is there a way to trap a vba code in an error loop?

Question

I'm working on a program using both Matlab and Excel. Excel gives its data to Matlab with a text file and then, once Matlab has finished its analysis, it creates a text file to be read by Excel.

In my vision of the program, I would like to have only one sub to do everything. Therefore, I wanted to put the sub on hold to wait Matlab until the file is ready. There comes my problem, because there's no file before Matlab finishes, trying to open the file will result in an error.

I tried to use the error handler to make a loop but it appears that VBA doesn't allow it.

Here's my test code:

Sub Test()

    Fichier = "'filepath'\file.txt"
    IndexFichier = FreeFile()
1:
    On Error GoTo expected   
    Open Fichier For Input As #IndexFichier

    Close #IndexFichier
    Exit Sub

expected:
    Err.Clear
    Close #IndexFichier 'Don't know if useful'
    GoTo 1


End Sub

I expected the code to loop between "1" and "expected" but after one loop vba breaks on its own with the expected error "file not found". Does anyone know if there's a possibility to loop on errors or do I have to make another sub?

Try Resume 1 rather than GoTo 1 .. Here's a good source of info cpearson.com/excel/ErrorHandling.htm — Tin Bum
– Tin Bum, Commented May 8, 2019 at 13:47

Damian · Accepted Answer · 2019-05-08 13:46:12Z

1

You don't need to wait for an error, this is what you are looking for:

Ruta = wb.Path & "\"
Exportar = Ruta & "datos" & Servicio & ".txt"
FindIt = Dir(Exportar)
While Len(FindIt) = 0
    FindIt = Dir(Exportar)
Wend

answered May 8, 2019 at 13:46

Damian

5,1741 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steven Carlson · Accepted Answer · 2019-05-08 13:45:53Z

0

You can have the code check to see if your path exists or not. If not, have it wait.

Do While Dir(Fichier) = ""
    Application.Wait(Now() + TimeValue("00:00:10"))
Loop

answered May 8, 2019 at 13:45

Steven Carlson

835 bronze badges

Collectives™ on Stack Overflow

Is there a way to trap a vba code in an error loop?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related