Save file to a specific shared drive/folder using a macro.

Question

I have an Excel workbook that has a macro that saves a file to a specific location when the file is opened.  My problem is that I am saving the file to a shared drive and it won't let me specify an existing folder in that shared drive.  I can only get it to save at the root of that drive.  I am using the following code:

Sub Auto_Open()

Range("Client!L10").Value = Date '

Auto_Open Macro

ActiveWorkbook.SaveAs Filename:="P:" & Range("test_final!A17").Value & ".xlsm"

End Sub

What can I do to save it to a folder called "Access Generated Files"?  Thanks in advance!

Answer 1

Did you omit the slash at the end of - "P:\Access Generated Files" ?

<sg0824> wrote in message news:*** Email address is removed for privacy ***...

Thanks for your quick reply. I had it set up the way you suggested originally and instead of putting it in the P drive in the folder called Access Generated Files, it just saved it in the P drive with the file name Access Generated Files and then whatever the contents of A17 are.  I did not try putting the folder name in A17, however.  I'll give that a shot and see what it does.  I didn't know if having it in a shared drive would make a difference - it worked fine when I saved it to the C drive.

---

Graham Mayor - Word MVP

www.gmayor.com

Posted via the Communities Bridge

http://communitybridge.codeplex.com/

Answer 2

Thanks for your quick reply. I had it set up the way you suggested originally and instead of putting it in the P drive in the folder called Access Generated Files, it just saved it in the P drive with the file name Access Generated Files and then whatever the contents of A17 are.  I did not try putting the folder name in A17, however.  I'll give that a shot and see what it does.  I didn't know if having it in a shared drive would make a difference - it worked fine when I saved it to the C drive.

Answer 3

What are the contents of Range("test_final!A17")?

Does it start with

Access Generated Files\

If it contains just the file name without the path, you will need to use

ActiveWorkbook.SaveAs Filename:="P:\Access Generated Files" & Range("test_final!A17").Value & ".xlsm"

-- Hope this helps.

Doug Robbins - Word MVP,

dkr[atsymbol]mvps[dot]org

Posted via the Community Bridge

"sg0824" wrote in message news:*** Email address is removed for privacy ***...

I have an Excel workbook that has a macro that saves a file to a specific location when the file is opened.  My problem is that I am saving the file to a shared drive and it won't let me specify an existing folder in that shared drive.  I can only get it to save at the root of that drive.  I am using the following code:

Sub Auto_Open()

Range("Client!L10").Value = Date '

Auto_Open Macro

ActiveWorkbook.SaveAs Filename:="P:" & Range("test_final!A17").Value & ".xlsm"

End Sub

What can I do to save it to a folder called "Access Generated Files"? Thanks in advance!

---

Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org