Alternative to the FileCopy Statement for open files?

[broro183]

hi all,

I'm sure I have seen a workaround/alternative (to FileCopy) technique for copying files that another user may have open at some stage in the past but when I google all I can find is examples of code looping until the file is closed & then performing "FileCopy source, destination".
Are there any alternative techniques that will allow a copy to be taken of a file on a shared drive that another user may have open without having to have my code "pre-open & savecopy as"?

The reason I'd like to do this is to allow for (5) network files to be copied to a local (c drive before performing various filtering/extraction actions on each file. The consolidation/summarising code I currently have runs in about 3 minutes* when tested on my C drive but "Network speed" (?) has caused it to take 30-40 minutes + when using the live network files.
The files are weekly Production schedules that are created/controlled by Site Planners & may still be open by a number of users for review when I wish to complete the consolidation (after Planners let me know that they are saved/set for the week).
*All possible "Application.xyz = false" precautions have been taken to assist with code speed & reset.


I'm off to work now & can't reply from work but will be able to answer questions with any further detail tonight.
tia

Rob

[broro183]

hi all,

I've done a lot more reading and am currently going to declare & use the File scripting object to work around this problem.

I've adapted the below code from Ron DeBruin's below link & it seems to work so far - even on open files:
(macro name the same, other bits changed/deleted)
http://www.rondebruin.nl/folder.htm#Copy

Please Login or Register  to view this content.

This approach has worked in my limited tests so far but I am wary of using fso due to various comments I've read (esp the below thread which warns of fso speed (ie opposes my main goal of speeding up code execution) and or user/network permissions to MS scripting - hopefully it works at work...).

http://www.tech-archive.net/Archive/.../msg01857.html

hth anyone else who may have similar problems.


If anyone can suggest a better alternative to VB's "filecopy" or "fso.copy file" please do...

Thanks
Rob

[shg]

Rob,

The link to the discussion on the FSO was interesting, both regarding speed and security issues.

Minor comment: The code you posted shouldn't (doesn't) require a reference to Microsoft Scripting Runtime, because it uses late binding. Changing the code to use early binding does:

Please Login or Register  to view this content.

The advantages of early binding are that you get IntelliSense and a modest improvement in speed.

Alas, that doesn't address your main question.

[broro183]

Thanks Shg,

Good point about the early v late binding, I wan't paying enough attention when I adjusted the existing code - together with juggling the loose bits of info that are floating around my head after trawling through this 'n that trying to find an answer.

I'll convert to early binding - I was able to make the reference today & I'll take anything even if it is only a modest improvement :-)
As you say, alas!
It's not the main answer but at least I have a work-around.

Yes, I thought it worth providing the link b/c I've read a lot about this in the past couple of days & although many posters were negative about FSO he was the only one who fully explained his logic.

Rob