kill code with file not found error

[cwchan220]

Hi,

I am trying to delete a workbook on the shared network and I am getting runtime error 53: file not found. Debug shows the error on the kill line. I've tried the following code on a test file that resides on my desktop and it worked fine. I also checked for the full name of the file on the shared drive via msgbox and it seems to be correct so I can't figure out why it doesn't work. I can delete the file manually so it's not an access issue. Ideally, I want the code to execute when the user attempts to open the file after a specified date. Does anyone know if it's an issue with shared network files and if there's a way to make this work? Any help is appreciated!

Please Login or Register  to view this content.

I have also posted this question on http://www.mrexcel.com/forum/excel-q...ok-open-2.html

[shg]

What does Len(Dir(.FullName)) return?

[cwchan220]

If I just do msgbox Len(Dir(.FullName)), it gives me runtime error 52: bad file name or number.

[shg]

Please Login or Register  to view this content.

What do you see in the Immediate window?

[cwchan220]

It gives me the same filepath and name as the msgbox: "https://global.shareddrive.com/Document Center/US/Reports/Reports.xlsm" and runtime error 52: bad file name or number

[shg]

That's a URL, not a network share. Out of Kill's skills.

[cwchan220]

That's the directory's path when I copy/paste the path from the folder. Maybe it's the way the company is set up then since I think it's using Sharepoint In any case, is there another way to expire the file?

[shg]

Dunno. That's a Sharepoint question, which I have zero experience in automating.

Perhaps if it's accessible as a shared drive ("Q:\DocCenter\...").

[cwchan220]

"Q:\Document Center..." doesn't work. If kill doesn't work, I'm assuming delete won't as well?

[shg]

I said shared drive, I meant mapped drive. You would have to create the mapped drive.

[cwchan220]

Thank you for pointing me to the right direction, shg. I am reading up on this topic and most likely open a new thread for further help.

[shg]

You're welcome.

I'd suggest your next question be specifically with regard to SharePoint. I know this is doable, but don't know how.

[cwchan220]

I opened http://www.excelforum.com/excel-prog...-intranet.html but meddling with Sharepoint might be too difficult a feat for me. I'm thinking an alternative can be to just delete the code within the file which will render the file useless. I'm assuming this can bypass the URL problem since I don't have to name its path? Should I open a new thread for help with that or do you know how to achieve that? My Master workbook has the code to open a new workbook and once the new workbook is opened, I want to direct the code back to the Master workbook and delete itself.

[shg]

Retreat several steps and explain your objective.

If it is to defeat the functionality of a workbook on a company SharePoint, you'll need to look for help elsewhere.

[cwchan220]

Sorry if it came off that way. Perhaps there is a better way of working around this that I didn't think of.

I put these files on the shared drive which happens to be Sharepoint on a "trial" basis so I don't have to worry about emailing each old and new user the file especially if I have to make minor updates. However, my original intention was to delete the file when the user tries to open it after a specified date or condition (when there's a newer file.) These files are a little bit of a mess so updating them becomes a headache and as new data is added, I'm trying to take bits of what I need from the old file and create a new file. As I am testing the new file, the trial one needs to continue to work. I wanted to make it easier for myself to use the kill code so I didn't have to track each file to go back and delete. Since that doesn't work, I was hoping to just remove the code when it meets those conditions. Hope that clarifies.

[cwchan220]

Hmm I was focusing on making old files not functional that I forgot that over time useless files will accumulate if I just remove the code and leave the shell. I will try to think of another solution.

[shg]

If you want to keep the workbook current, why not just save over the old version with the new?

[cwchan220]

If I could kill the file with an expiration date, it will allow the user to transition between the versions. As for the files that are useless when there's a newer file, I could save over the old but I've had a coworker use 'save as' to put it on her desktop instead of creating a shortcut of the original. It will be hard for me to tell who's using the incorrect file until an error happens. My concern is preventing the user from using the outdated file. As for Sharepoint, I believe all versions are backed up so IT should be able restore any of the files. For the time being, I'll continue to manually update and delete the files.

[shg]

You could put code in the file that checks for a newer version on SharePoint and prompts the user to go get it.

[cwchan220]

Just wanted to follow up after trying your suggestion for the past few weeks. So far no glitches yet, thanks again!!