Saving file as returns error "424": object neccessary

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
Hi there,
I have a macro to create a worksheet and it is not saving the file returning the error 424.

In my code I define the folder and the file name.

Dim Folder As String
Folder = "A:\Controlling\2014\Reporting\CARS\Template\CARS\06_Jun\"

Dim RSM As String

Selection.Offset(0, 2).Select 'cell in file where RSM name
RSM = Selection

Dim timestamp As String
timestamp = Format(Date, "yyyymmdd")

Dim NBName As String
NBName = "CARS_" & RSM & timestamp & ".xlsm"

ActiveWorkbooks.SaveAs Filename:=Folder & NBName _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

when I scroll over the defined names in the last code it shows the following:


-- removed inline image ---


-- removed inline image ---

I am not sure why the file is not saving and which object is missing.

Regards
Sarah
 
Well, wherever your lines of code appear, your original issue was that as well as Norie's suggestion to change ActiveWorkbooks to ActiveWorkbook, you need to create your filename by concatenating dates, cell contents and string values into one string variable, which you then use as a paramater in your ActiveWorkbook.Save command.
With things like this, I also ways find it handy to add MsgBoxes to my code, so you can see the values of variables as you go along. Try adding
Code:
MsgBox (MyFilename)
immediately before your ActiveWorkbook.Save command, so you can see which bits of your filename haven't been added in correctly. Without all the correct bits, it may amount to an invalid filename.

Of course, it always helps to post the relevant code in its entirety..! :-)

whats happening is this error runtime error '1004':
the method 'SaveAs' has failed for the Object'_Workbook' but the file open has been renamed. The msgbox shows the correct folder&file name.

My code is quite long (lots of copy paste to try and save manual transferring of data. Not sure where and how to add the code. I guess I don't know how to add using code tags either.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
one other thing, the file has correctly been renamed as desired but it is not saving

-- removed inline image ---


could it be to do with the fact that my folder lies on a server which is not defined?
(\\daenas01.eur.ad.sag)(A:)
 
Upvote 0
Select and copy the code then paste it into a reply.

To apply code tags select the code in the reply window and click the # button.
 
Upvote 0
Norie - thanks for that last tip about the # button - I didn't know that!
Confused - so once you corrected your folder def, your code worked without my concatenation? Just curious.
Glad you got it sorted out though - good luck for the World Cup! :-)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top