+ Reply to Thread
Results 1 to 8 of 8

Save as Failing then Working

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,426

    Save as Failing then Working

    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)?
    Last edited by jomili; 10-11-2011 at 12:57 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,426

    Re: Save as Failing then Working

    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.

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,426

    Re: Save as Failing then Working

    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.

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,426

    Re: Save as Failing then Working

    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!

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656

    Re: Save as Failing then Working

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

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,426

    Re: Save as Failing then Working

    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?

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656

    Re: Save as Failing then Working

    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.

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,426

    Re: Save as Failing then Working

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1