autoit script and excel are in conflict when handling the same file

[DaisyDuke]

Hi, this isn't probably the right place to ask but I found this community very helpful and full of awesome fellas, I am sure someone got round to my problem once in his life because it's a quite general file handling situation with excel.

In few words, I have an autoit script that write some data in a text file at a random frequency and an excel file that repeatedly (every 4 seconds) load the data via the import external data query function. Sometimes the excel query stops reading the file, only reloading excel it gets back reading the file. So there is something that causes excel to lose its reference to the file. It could happen that excel reads the file at the same time of the function fileWriteToLine is executed. Also it accurs that the function FileCountLine executed before the writeToLine to get the last line to write to, gives me an error, probably because it reads the file while excel is already reading it. Well these are my thoughts, I am not an expert. So my question is:

is there a way in both excel and autoit script to check if the file is available before reading or writing to it?

I could sincronize the reading and writing with the system clock so they never coincide, but it seams an extreme solution and I don't know if it would solve the poblem.

There could be a different way to pass the data to excel, someone could say why don't you write it directly onto the worksheet? well I managed to write only on the active worksheet, I need something to upload data in background so I can use and activate the other worksheets. I hate excel but it's quick for easy data analysis and graphs.

Thanks for reading so far, any cool ideas?

[kylomas]

DD,

If both of these processes run on the same system you might try _WinAPI_FileInUse. Download the "WinAPIEx UDF for documentation. However, this will only help the process that is writing the file (your script). The reading process is still subject to file sync problems unless can put an autoit wrapper on the reading process and use the same technique to serialize access.

Good Luck,

kylomas

[DaisyDuke]

Thanks kyloma, I will try your advice, sure there will be something similar in excel VBA to do the same fileInUse check.

this forum ROCKS!

bye

Edited March 7, 2012 by DaisyDuke