Need Code to Check if .CSV file has finished writing

[Saladsamurai]

I have a code that I am working on. It is primitive and inefficient and that is fine with me.

The code loops and each loop it does the following:

1. Writes a .BAT file and stores it the variable "SolverBat"
2. Calls an application using Shell(SolverBat)

The application itself then solves a simulation and writes the data to a file called "Results.csv" This name is the default and cannot be changed.

3. Loops again and thus Overwrites "Results.csv" with new file of same name.

I want to be able to tell when "Results.csv" has been completely written so I can loop through it and collect the results.

Note

It is not enough to just check to see if "Results.csv" exists. It must also be checked (perhaps in a loop of sorts) to make sure it is finished.

Any thoughts?

Sub BatchSolve()

Dim fNum As Long
Dim InstallDir As String
Dim BookPath As String
Dim SolverBat As String
Dim i As Long

For i = 1 To 1
fNum = FreeFile()
InstallDir = "Z:\Casey_B\from Jon\PDML\Test"
BookPath = Workbooks("Test.xls").Path & "\"
SolverBat = BookPath & "Solve.Bat"


Open SolverBat For Output As fNum

Print #fNum, "call" & Chr(34) & "c:\Program Files\MentorMA\flosuite_v82\flovent\WinXP\bin\flovent" & Chr(34) & " -env"
Print #fNum, "echo Start"
Print #fNum, "call" & Chr(34) & "c:\Program Files\MentorMA\flosuite_v82\flovent\WinXP\bin\flovent" & Chr(34) & " -b" _
& " Test" & i & " -O Z:\Casey_B\from Jon\PDML\Test\TestCSV"
Print #fNum, "echo " & i
Close #fNum

Shell (SolverBat)

Start Checking on "Results.csv"
If it exists and finished writing Then
Collect data
Else Check again

Next i

End Sub


Also: I don't undersatnd what freefile is or does? I read the help, but it is vague.
What would happen if I just used some number like #3 or something?

[Tinbendr]

I think you'll have to write an error checking routine and test whether the file can be opened for output which would generate an error if it is still open. Poll the file every 5 sec or so.

Freefile assigns the next available filenumber from the DOS. I just keeps things dynamic. You could change all of the fNum to #3 and it would 'probably' run ok. It's a hit or miss if there is another filenumber 3 open at the time.

[Saladsamurai]

I think you'll have to write an error checking routine and test whether the file can be opened for output which would generate an error if it is still open. Poll the file every 5 sec or so.

Freefile assigns the next available filenumber from the DOS. I just keeps things dynamic. You could change all of the fNum to #3 and it would 'probably' run ok. It's a hit or miss if there is another filenumber 3 open at the time.

Okay great! I think that I get the FreeFile thing now. I guess that I am just not sure how there could ever be another file #3 open? What constitutes as a 'File' ? I am not familiar with the inner workings of my PC and DOS. I just hit the keyboard till it does what I want :)

Also. for anyone interested in how we solved this:

The number of files in the csv file is predictable. So in my For-Loop I have added

Do Until NumberOfLines(InstallDir & "Recirculation_smartparts.csv" ) = NumOfLines
Loop

Where NumberofLines is the function:

Function NumberOfLines(FileName)

Dim TextLine(10000) As String
Dim i As Long

Open PathName For Input As #12

i = 1
Do While Not EOF(12)
Line Input #12, TextLine(i)
i = i + 1
Loop
Close #12

NumberOfLines = i - 1

End Function


Don't ask why its '#12'

I copied that part over from something else. I suppose it would be smart to replace that with a variable fNum2 = Freefile() or something.