I am trying to get this file to be saved in a network drive mapped on my computer. I just want to save it and not close out the file or anything. What I have so far is listed below. I want the file name to be "Bread Reconciliation" + "Whatever date that is listed in cell D1 on Sheet "Bread Input" ". I keep getting this error: Run-time error'1004': Method 'SaveAs' of object'_Workbook' failed
Please Login or Register to view this content.
What's in Sheets("Bread Input").Range("BD1").Value?
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Its "=D1" and in D1 it is a date that is entered in the mm/dd/yyyy format (4/30/2019.
So in BD1 it references the short date and I added a custom format where it displays "Bread Reconciliation April 30 2019"
Here is the custom format I have "Bread Reconciliation" mmmm_dd_yyyyy"
When I have run into this issue in the past, it is usually one of the following errors:Originally Posted by Dscalf1
- You don't have Write permissions to that location (test by saving a non-excel file to that location)
- The ActiveWorkbook is not in the same format as the file extension you are trying to save it as (.xls)
If the above are fine, I would then check the following (These are unlikely but could occur)
- Does the folder path in FP still exist? Could it have been renamed or moved? Use a function to verify the save path location exists before the saveas
- Could the value in BD1 contain characters that are invalid for filenames in Windows? e.g. if variable FN contained a ":", the workbook won't save.
- Even if the previous point is fine, Excel has additional restrictions on workbook filenames. I have updated this post to include a function that will check this for you.
HTH
P.S. You can use this function to check if the path exists
You can use the code below to verify that the proposed fullname is valid for WindowsPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by mc84excel; 04-30-2019 at 08:22 PM.
So Im trying all these codes and functions and nothing is working Im positive the path is right and the folder is still there and I have editing rights to it because I can add and delete things out of it...
I tried the functions and im not sure if im using them right but i just keep getting value or and empty space back.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I can attach the document however I am trying to save this macro wouldnt do anything in the actual file only save the file in my network drive when a button is pushed..
Use Debug.Print FN after "FN =" line.
See what comes up in Immediate window. I suspect you'll see "mm/dd/yyyy" format date. Which contains illegal character for file name.
Use
Instead of .Value to use formatted cell text as is.Please Login or Register to view this content.
Last edited by CK76; 05-01-2019 at 04:52 PM. Reason: For clarity, added illegal char part
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
I got compile error expected expression
Okay so I used the record macro tool and got this code and it is working however how do I get it to change the name based on the date I have in cell BD1 with the words Bread Reconciliation in front of it. Instead of it saving as Bread Reconciliation v4 all the time I want it to save new files with a different name when they click the button
Please Login or Register to view this content.
SOLVED:
Please Login or Register to view this content.
They should both return True. If not, you need to change (or make) the save path and/or purge invalid characters from the filename.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks