importing a file already opened

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to automate a file import. Most of the time, the code below
works great. However, there are occassions when another user will have this
file open. Generally, it is only opened for several minutes or less.
However, this situation causes the macro to throw an error message.

vLoc = "V:\Invoices\Cost Center List.csv"
DoCmd.TransferText acImportDelim, "Cost Center Specification", "Cost
Center", vFileName, False

What I would like to do is create some loop that will allow the macro to
keep trying to import the file until it is successfully imported. I really
don't need to see the error messages at all. I would like this situation to
be transparent to the end user.

Any suggestions or code to get me started would be greatly appreciated.
Thanks for the help........
 
JT,
Try using error trapping with a Do...While function.
You'll need to know the error number and if that is returned, the procedure
will loop until the error number isn't returned. You may also want to put a
counter in so that after, say 250 attempts, it will end the loop in case
someone has left the file open and gone home.

Dave
 
A timeout might be better than a fixed loop count:

(untested)

dim x as single
x = timer()
do
if timer() - x > 300 then
msgbox "5 minutes up!"
exit do
endif
(do something)
doevents
loop

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Back
Top