VBA Save As

[Mudbutt]

I'm having trouble saving as. I have a button built into a worksheet that runs a macro that is within the "ThisWorkbook" module on the backend of the workbook. Everything is running fine until it gets to the very last line that saves it as a different name in a different folder. Can I not do this?

ThisWorkbook.SaveAs "...\" & Format(Date, "yyyy") & "\Domestic\Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx"

 

[almagg]

you have to create the Directory first if it does not exist.
and i believe you have to 'build' each new folder separately.

e.g.

MkDir [current path] & "\" & Format(Date, "yyyy")
MkDir [current path] & "\" & Format(Date, "yyyy")& "\Domestic"

ThisWorkbook.SaveAs [current path] & "\" & Format(Date, "yyyy")& "\Domestic\" & "Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx"

or
newpath = [current path] & "\" & Format(Date, "yyyy")& "\Domestic\"

ThisWorkbook.SaveAs newpath & "Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx"

 

[Mudbutt]

I'm not creating a new folder, the folder exists already. I put in the "..." to cover my folder names so I don't disclose anything about our company. The folder path is correct. I guess it's the syntax...

 

[Joe4]

Can you post where it is saving it to and what it is named, versus, where is SHOULD be saving it to and what it SHOULD be named (you can comment out the firsts parts again)?

 

[Mudbutt]

I am opening a template file, so it's a generic name called "Domestic.xlsm".

So then someone is suppose to click the button I created, which in turn, runs a macro that is in the "ThisWorkbook" module.

At the end, it is suppose to save the file name as "Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx".

The folder is: \\...\Weekly Fulfillments New\" & Format(Date, "yyyy") & "\Domestic\

 

[almagg]

from the first post it looks like you missed a quotation
.....................................................................................here
ThisWorkbook.SaveAs "...\" & Format(Date, "yyyy") & "\Domestic\" & "Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx"


you had
.....................................................................................here
ThisWorkbook.SaveAs "...\" & Format(Date, "yyyy") & "\Domestic\Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx"

 

[Mudbutt]

No, the way I'm writing the file name is correct. I use it in other modules; however, i tested your theory regardless and I'm still getting an Application-defined or Object-defined error message.

 

[Mudbutt]

It's almost like ThisWorkbook.SavAs does not work when it's built into the workbook rather than having the module on my PERSONAL.XLSB file. I need the code actually within the workbook though.

ThisWorkbook.Save works, but not SaveAs with a path. Do i need to reference it differently?

 

[almagg]

i just did this and i got a "...file extension..." error.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.SaveAs "C:\Documents and Settings\Owner\My Documents\Downloads\" & "Test.xlsx"
End Sub

using 2007 a file with code cannot be saved as .xlsx

when i changed the extension to .xlsm it worked

that is about all i know.

 

[Mudbutt]

Wow, I'm a moron. Thanks!