CODEWITHSUNDEEP
excelvbafilepath
user3139969
user3139969

Reputation: 13

excel file is not saving to the macro specified filepath

I have a simple macro that I want to use daily, it works fine other that it does not save where I would like it to.

Rather than saving in the desired shared network folder, it is saving in 'Documents' folder. Please help.

Dim FilePath As String
Dim NewName As String

FilePath = "G:\Pricing\Gas Pricing Models\Wholesale\Basis Strips": NewName = "NYMEX" & Format(Date, "MM-DD-YYYY") & ".xlsm"

ActiveWorkbook.SaveAs Filename:=NewName, FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

ActiveWindow.SmallScroll Down:=-30
End Sub

Upvotes: 1

Views: 3417

Answers (1)

ARich
ARich

Reputation: 3279

The Filename argument of the SaveAs method should also include the filepath of the file. If you don't include the filepath in that argument, the file is saved to the current folder. See this page for more information.

Your code should look like this, then:

Untested

FilePath = "G:\Pricing\Gas Pricing Models\Wholesale\Basis Strips\"  
NewName = "NYMEX" & Format(Date, "MM-DD-YYYY") & ".xlsm"

ActiveWorkbook.SaveAs Filename:=FilePath & NewName, FileFormat _
    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Note the added backslash at the end of the FilePath string.

Upvotes: 3

Related Questions