Workbook.SaveAs Eror Message with Alphnumerics for Filename

  • I'm about as frustrated as I've ever been with Excel, and I'm counting on some of the brilliant Ozgrid minds to resolve a really sticky problem.

    I've written code for a part of an application I'm developing to copy the active worksheet, make a few changes to the new workbook, and saving it to a different path than the original workbook. Here's the line of code that's intended to save the new workbook:

    ActiveWorkbook.SaveAs ("C:\Users\Daryl\Insurance\Medical\Medicare\Claims\2015\Claim Summaries\20150129 Matthew M Thompson MD Office Visit.xlsx")

    Everytime I run it, I get a run-time error 1004 message that says "Microsoft Excel cannot access the file", followed by the path and filename. The filename is where the problem appears to be: ''C:\Users\Daryl\OneDrive\Insurance\Medical\Medicare\Claims\2015\Claim Summaries\1FDBE200'. As you can see, there are 8 alphanumeric characters (different each time) where the filename is supposed to be. When I step through the macro in the VBE Locals window, the filename displays correctly.

    The code initially had references to formulas for the "2015" folder, and to three separate formulas making up the file name. I wrote it out letter-by-letter for testing purposes. I have now done an "Open and Repair" on the original file, and after confirming the problem occurred with other workbooks and file paths, both a "Quick" and "Online" repair of Office 2013 from Programs and Features. None of those has helped.

    Any resolutions or even best guesses you'd care to offer will be greatly appreciated. Thanks

  • Re: Workbook.SaveAs Eror Message with Alphnumerics for Filename

    Well I have no insights....

    1. I know there are size limits to file names / paths ( though I don't think you're there )
    2. As a practice I NEVER EVER use spaces in my file names.
    3. Try recording a macro and then do the "SAVEAS" by hand. Check the code and see what pops out at you.

  • Re: Workbook.SaveAs Eror Message with Alphnumerics for Filename

    I really appreciate the suggestions, but turns out it wasn't a problem with Excel at all. In checking along the path, I discovered that the copies of files I save to my OneDrive still have the old SkyDrive as part of the path in their properties. I still need to wrestle that one to the ground with Microsoft, but in the interim I did a few tests of Workbook.SaveAs using the macro recorder and discovered I can save directly to OneDrive online with the URL to my account included in the path. Thanks again!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!