Possible To Get File Completed Posting Info? (1 Viewer)

[catbeasy]

Hi
I have some code that checks if a file exists, its file size, creation date and date modified. The code works fine..however..

I need to import the file it is checking and that file can sometimes take up to 30 minutes to complete its posting. In fact I used the code to check it the other day and the code showed that it exists, its creation/modifed date and also its file size - however, the file size was what it was at that moment I ran the code. If I immediately re-run the code, the file size changes - obviously due to the fact that the file is still posting (and growing due to the date being posted to it). Also note that the (completed) file size will vary each day the file is posted

So, what I would like to know is, is their a way to check to see if a file has completed posting? Something akin to .filecreated like .filecompleted (yes or no) type thing?

Any help is appreciated. Here is the code:

Public Sub sub_chk_files2()
Dim obj_fs As Object
Dim obj_folder As Object
Dim obj_files As Object
Dim obj_f1 As Object
Dim db As DAO.Database
Dim str_fp As String
Dim str_fd As String
Dim str_fqfp As String
Dim str_date As String
Dim str_prod As String
Dim int_mod_dt As Integer 'number of days (from current day) that defines the date - in the file name - of the ITG file that is to be imported..
Dim int_count_files As Integer
Dim str_import_file_name As String
Dim lng_file_size As Long
Set db = CurrentDb
str_fp = "C:\01_reporting\"
Set obj_fs = CreateObject("scripting.filesystemobject")
Set obj_folder = obj_fs.getfolder(str_fp)
Set obj_files = obj_folder.files
For Each obj_f1 In obj_files

If obj_f1.Name = "data2.csv" Then

str_import_file_name = obj_f1.Name
str_fqfp = str_fp & str_import_file_name
lng_file_size = FileLen(str_fqfp) / 1000

MsgBox "File " & str_import_file_name & " was created on " & obj_f1.DateCreated & " File size = " & lng_file_size & " File Modified on " & obj_f1.DatelastModified
End If
Next
End Sub

 

[vbaInet]

I guess therein lies your clues...

...its creation/modifed date and also its file size - however, the file size was what it was at that moment I ran the code.

1. Do a file size check and/or a date modified check between now and 1 minute later
2. If there's a difference, wait for however many minutes
3. Perform the check again, and if it's still growing wait for however many minutes
4. Count how many times you've done the check and how many minutes you've waited and decide whether to attempt using the file.

You should already have an indication when the file gets posted and how long roughly it takes to complete so run the checks after your estimated post completion process.

And for a quick file size check in VBA, you can use FileLen() if you don't want to involve the FSO.

 

[pbaldy]

Another thought would be to see if the process used to "post" the file can use a different name for it, then change the name at the end. That way this code wouldn't see the file until it was complete.

 

[Bilbo_Baggins_Esq]

you could also access a windows API and monitor the task

 

[catbeasy]

I guess therein lies your clues...

1. Do a file size check and/or a date modified check between now and 1 minute later
2. If there's a difference, wait for however many minutes
3. Perform the check again, and if it's still growing wait for however many minutes
4. Count how many times you've done the check and how many minutes you've waited and decide whether to attempt using the file.

You should already have an indication when the file gets posted and how long roughly it takes to complete so run the checks after your estimated post completion process.

And for a quick file size check in VBA, you can use FileLen() if you don't want to involve the FSO.

Yeah, obviously trying to avoid this if possible..though from the responses it sounds like there isn't anything that can be done from Access VBA..

btw, I did use FileLen - see the end of my code..

Thanks for your assistance!

 

[catbeasy]

you could also access a windows API and monitor the task

Do you have any references or anything I can use to get started on this? I searched the web, but there didn't seem to be anything specifically geared to what I need regarding the completion of a file being posted..

Thanks..

 

[vbaInet]

Yeah, obviously trying to avoid this if possible..though from the responses it sounds like there isn't anything that can be done from Access VBA..

btw, I did use FileLen - see the end of my code..

Thanks for your assistance!

Since you were using fso from the start I thought you were going to use the Size property of the File object as well so I assumed you were doing that.

Just going to add that even though you can monitor the process in the Task Manager through perhaps WMI, you still need code that will continuously check it every so often.
So why spend more time looking for extra APIs when you can do the continuous check on the file size? If you were going to physically look at the the Task Manager then fine to monitor the stats of the process then fine.

So at the end of the day if it needs to be done programatically, a continuous check needs to be done and since VBA doesn't have threads you have to use the form timer.

 

[catbeasy]

Since you were using fso from the start I thought you were going to use the Size property of the File object as well so I assumed you were doing that.

Just going to add that even though you can monitor the process in the Task Manager through perhaps WMI, you still need code that will continuously check it every so often.
So why spend more time looking for extra APIs when you can do the continuous check on the file size? If you were going to physically look at the the Task Manager then fine to monitor the stats of the process then fine.

So at the end of the day if it needs to be done programatically, a continuous check needs to be done and since VBA doesn't have threads you have to use the form timer.

Interesting, yeah, I may look into doing the timer thing..

Thanks for your response!

 

[vbaInet]

Or if you want to do things properly then write a script and run it as a scheduled task.