Save as Failing then Working

**jomili** · 10-04-2011, 05:18 PM

My macro creates 20 workbooks, then saves each one to our Sharepoint drive. But for some reason today I'm getting an error code "1004 vba method saveas of object _workbook failed" during the save. The below code is representative of the code I'm using.

Please Login or Register  to view this content.

. If I hit "Debug", then "Continue", it goes without a hitch, then fails on the next one. I'm just using the one line to do the save as. Any ideas why the code is failing (but obviously it still works)?

**jomili** · 10-05-2011, 09:36 AM

Okay, no response yet. I thought maybe if I posted some more of my code it would help. I can't post the WB because it's too big. I just added the lines for "Application-Display Alerts", but they haven't helped. I could sure use some advice on this one.

Thanks,
John

Please Login or Register  to view this content.

**jomili** · 10-05-2011, 09:59 AM

I just did a test; if the workbook already exists on the Sharepoint drive, the error happens. If it doesn't yet exist, the error doesn't happen.

**jomili** · 10-05-2011, 12:12 PM

Hmm, and now I just ran another test, without changing my code in any way, and it appears to be saving just fine. What's up with that? I've been making sure I fully close out Excel every time, and I've run Code Cleaner a few times just to be on the safe side. This stuff messes with my head!

**shg** · 10-05-2011, 12:54 PM

Never worked with VBA and SharePoint, but you might try something like this:

Please Login or Register  to view this content.

**jomili** · 10-05-2011, 01:05 PM

SHG,

I'm willing to try it, but am unsure of what it does.

I assume sFile is the path to save the file to, including the workbook name. The line

Please Login or Register  to view this content.

looks like it would convert the file to a read-only file. But then you have

Please Login or Register  to view this content.

which I think would tell VBA to ignore/forget what sFile is. So then

Please Login or Register  to view this content.

doesn't make sense to me.

Will you please enlighten me?

**shg** · 10-05-2011, 05:23 PM

Changing the file access mode is what allows you to delete the file, if it is the active workbook.

The Kill deletes the file if it exists, it doesn't affect the variable sFile.

Back up everything first. Killed files don't go to the recycle bin.

**jomili** · 10-11-2011, 12:58 PM

I think my problem may have been with Sharepoint and not Excel. Everything seems to be working fine now. I'll keep this code in my pocket in case I have trouble again.

Thanks,
John