Saving File automatically

D

dok112

Hello,

I am using the below to save a file to a drive. I want it t
automatically save it in the folder (textbox1) based on what is entere
into a userform. But I am getting an error when it is saving the file.
It say's the folder name has already been created so it won't save th
document there. How can I bypass this? The form is going to b
entered on a daily basis, and I need it to save the files in th
specific folder based on the textbox1 input.

newFolder$ = "\\Pkcfp009\mait_e&d\NewHireDocumentation\Transition t
Success Plans\" & TextBox1.Text
MkDir newFolder$
newFile$ = newFolder$ & "\" & TextBox2.Text & ".xls"
ThisWorkbook.SaveAs newFile
 
M

Mark Thorpe

At the top of your subroutine, use "On Error Resume Next". If the folder
already exists, this will allow you to skip past the error. Also, use
"Application.DisplayAlerts = False" before calling SaveAs if you don't want
Excel to bring up the prompt asking user if they want to overwrite the
existing file. With DisplayAlerts set to False, Excel will automatically
overwrite.

One last thing - if your code below is part of a larger subroutine, you
should split it off into a separate subroutine and call it from the larger
subroutine. Otherwise, if you put "On Error Resume Next" at the top of the
larger subroutines, you could be skipping past errors that you'd normally
want to catch.
 
K

kkknie

You don't really need to put in another subroutine, since you can ge
rid of the on error resume next by adding the line:

On Error GoTo 0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top