Macro Save As - Wrong Location

debraanne16 · #1 (**permalink**) 11-02-10, 16:19

Hi

I'm using this macro to save a file with a title and today's date to a specific location.

Dim newFile As String, fName As String
fName = Range("B1").Value
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
ChDir _
"X:\Common\Service Centre\End of day reports\Current Report"
ActiveWorkbook.SaveAs Filename:=newFile

However on some occasions this macro is saving to My Documents rather than the file path shown above.

Can anyone help? Thanks

Colin Legg · #2 (**permalink**) 11-03-10, 05:40

Hi,

Try including the path in the FileName argument.

Code:

Dim newFile As String, fName As String
 
fName = Range("B1").Value
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
 
ActiveWorkbook.SaveAs Filename:= _
    "X:\Common\Service Centre\End of day reports\Current Report" & newFile

debraanne16 · #3 (**permalink**) 11-03-10, 14:07

Thanks, but the result is as follows:
- saving at X:\Common\Service Centre\End of day reports
- "Current Report" is now part of the file name

Colin Legg · #4 (**permalink**) 11-03-10, 16:42

My mistake, you need a \ after Current Report like this:

Code:

ActiveWorkbook.SaveAs Filename:= _
    "X:\Common\Service Centre\End of day reports\Current Report\" & newFile

Internet Services

Web Development

Digital Marketing

Consumer Tech