See when transferspreadsheet finishes

[spikepl]

I export some spreadsheets where I after export have a routine to autofit the columns: it opens the spreadsheets, autofits and closes again.

I have a small delay loop in the code between the transfer and the autofit, as experience showed that the program control can move onto the next statement and try to open the excel file but the file is not quite ready yet.

Today I had a heavy query, so my standard delay was not enoough. That was a patch anyway. So how can I in code see (or ensure) that Access has finished fiddling with the Excel file?

 

[gemma-the-husky]

I suspect that dir(filename) would return "" until the export completes

so this might work

docmd.transferspreadsheet fname etc
while dir(fname)=""
   doevents
wend

 

[spikepl]

That did not fix my problem, but I may have misdiagnosed the thing! It will take some time to verify what is what. Thanks for the suggestion. I'll be back.

 

[Rx_]

Let us know. I don't use the Access reports. Instead the Excel Object Model is used for reports. Some reports are close to the maximum size. They have a huge amount of formatting applied with Excel standard functions plus vba custom functions.

The Excel formatting have always run in a linear process.

This is something I do out of habit. I use to teach the Microsoft Excel Object Model Programming course back in 1997. It was recommended by Eric Wells in his book.
For all of these years, I just do it out of habit. So, I can't promise it will make a difference.
objxl is a set to the excel application
objxl.EnableEvents = False
' all vba formatting and formula injection
- then set it back to true before doing the save as.

 

[spikepl]

The new problem was caused by code hanging: http://www.access-programmers.co.uk/forums/forumdisplay.php?f=3

 

[gemma-the-husky]

The link didn't work for me. Are you sorted now?

 

[spikepl]

Not quite. The thing simply hangs. Sorry about the link: http://www.access-programmers.co.uk/forums/showthread.php?t=270531