Solved: Automatic Save As at specified path

[AyeSee]

Hello hello,

What I am trying to do seems pretty easy to me, but I cannot seem to find an awnser. My initial search seems to talk mostly about merging.

What I am trying to do is simply save the file in a specified path as soon as the macro is started. The file name is determined as the first line of the text, and it seems relatively straightfoward with the Save As Method....

The lacking element of the saveas method (http://msdn.microsoft.com/en-us/library/microsoft.office.tools.word.document.saveas(VS.80).aspx)

Is that there does not seem to be a way to automatically choose the folder.

Does anyone know how I can achieve this?

Many thanks in advance,
Alex

[fumei]

I do not understand the question. What do you mean by "choose" the folder?

SaveAs has a parameter FileName. It must contain a valid path. So put a valid path into it.

Or do you mean that you want some method of choosing a folder first? If so, say so.

SaveAs is straight forward. It needs a valid path and filename.

[AyeSee]

Hey Gerry,

Thanks for the response. That is indeed what i meant. All I want to do is have it save at a specified path. I guess I misinterpreted the term FileName. So from what you are saying i can simply enter ex: "G:/Documents/AP1/" in the file name it should work?

I'll test it out... thanks!

[fumei]

Or, if it is going to be constant, declare a CONSTANT.

CONST MyPath As String = "G:/Documents/AP1/"

Sub Yadda()
ActiveDocument.SaveAs Filename:= MyPath & "whatever.doc"
End Sub
The file is saved as "G:/Documents/AP1/whatever.doc"

[AyeSee]

Wow! Thanks, thats exactly what I need. If I dont get things to work, this is what I'll do.

I have a String that contains the name of the specific folder I want it to be in, and i have another string with the specific name of the doc i want. Every case is specific (I will be storing docs for every new entry).

That being said, the folder will not be constant. Why declare it as a constant instead of a dim?

Thanks, this is all very interesting!

[fumei]

I just mentioned that IF it is constant, use a constant. If it is not...then don't. If there is some logic to putting a file in folder_X, and some logic to put a different file in folder_Y, then use the logic.

If you need the user to select a folder (rather than you setting a string value), then that can be done as well.

However, if you are using different string values for the folder, then there really is not much point in using a variable.
Dim FolderPath As String
FolderPath = "G:/Documents/AP1/"
ActiveDocument.SaveAs Filename:=FolderPath & "whatever.doc"
has extra steps. The above is no different - in value - from:
ActiveDocument.SaveAs Filename:= "G:/Documents/AP1/whatever.doc"
One step, as opposed to three.

[AyeSee]

Ok I understand. Does that mean I can do something like this?



Dim FolderPath As String
Dim SpecFolder As String
Dim FileName As String
FolderPath = "G:/Documents/AP1/"
SpecFolder = Selection.Text & "/"
FileName = Selection.Text & ".doc"
ActiveDocument.SaveAs Filename:=FolderPath & SpecFolder & FileName

[fumei]

Technically, yes.

I would NOT recommend this however.

What if you made a mistake and did not select anything??

Why do you have Selection.Text twice?Dim FolderPath As String
Dim SpecFolder As String
Dim FileName As String
FolderPath = "G:/Documents/AP1/"
SpecFolder = Selection.Text & "/"
FileName = Selection.Text & ".doc"
ActiveDocument.SaveAs Filename:=FolderPath & SpecFolder & FileName
Say you selected "dog", the file would be:

"G:/Documents/AP1/dog/dog.doc"

The current selected text is going to go into BOTH SpecFolder and FileName.

Are you sure about that?

If "dog" is not a valid existing foler, this will crash (fail).

Also, what if you selected text and included a space after - "dog "? Are you SURE your folder has that space? Or a space before?

So again, technically, yes you can do something like that...but it is fraught with potential errors.

Also, again if FolderPath is ONE value ("G:/Documents/AP1/" ) yes you can declare (Dim) a variable and then set it, but why? Use a constant. here is something that may tempt you. Say you have - oh, I don't know - five folders that your files are going to go to.
Sub SaveThisSucker()
Dim myFolders()
myFolders() = Split("G:/Documents/AP1/," & _
"H:/YogiBear/Montreal/," & _
"G:/Clients/HaveNotPaid/," & _
"H:/Bars/St_Laurent/," & _
"G:/Airports/Security", ",")

ActiveDocument.SaveAs Filename:= _
myFolders(Inputbox("Folder number please.")) & _
InputBox("Filename???") & ".doc"
You would get an InputBox asking for a folder number, and then an Inputbox asking for a filename.

You type "1" and Enter, "whatever" and Enter

you would get:


"H:/YogiBear/Montreal/whatever.doc"

The "1" is the second in the array (it is 0-based), and the ".doc" is appended onto the "whatever" you typed.


Or.....you could do it as a userform, and select from the list of known folders you are saving to. Demo attached. Click "Show Me" on the top toolbar.

[fumei]

I think my point is what do you mean EXACTLY by "specified" folder?

If it is one, use a constant.

If it is more than one, and you know what they are is, use an array.

If it is more than one, but you want the user (or you) to be able to select which one, use a userform with them listed.

If you have no idea, and want to be able to graphically select any folder, use a FolderPicker.

[fumei]

Here is a demo of using a folder picker. Note that you do NOT put a folder into the name box. Just select one, and click OK.

Click "Use FolderPicker" on top toolbar.

[AyeSee]

I think youve really hit the point, and your examples have much helped me understand how it works. Thanks a million Gerry.

I known exactly what you mean with the "Selection"... There is a risk that it selects nothing, but I have ensured that this is not the case. I can in all cases assume that Selection will return something valid.

Second, I understand it would return dog/dog.doc in this case. The actual code I will write will change the Selection so that it be the second required field. That being said, I can expect both selection fields to always be passed on to the VBA in the correct order.

The actual structure of the path will be: "G:/.../Vendor/ContractNumber.doc". That being said, the Vendor could be anything, and the contract number could be anything. In all cases though, they will not be null. (s**t, but you just made me think of special characters...)

[fumei]

"(s**t, but you just made me think of special characters...)'








and there ya go. Remember what you pass as a filename MUST MUST MUST pass what Windows finds acceptable as a valid path and filename.

Generally, I would avoid getting a Selection.Text for a filename. It is simply too prone for possible errors. Depending on any user to select appropriate text is, well, not fair to users. I suppose there are situations it is required, but as a general practice try to make critical strings - such as a filename - come from a narrow range of choices. Remember, technically, the user could select anything. Unless you are biulding rock solid error trapping, sure it could work out most of the time, but sometime...

[fumei]

" I can in all cases assume that Selection will return something valid."

Really? Post the code!

[AyeSee]

Ok. I'm currently doing everything we talked about. What I will do to ensure the special characters are well handled is I will impose limitations in the data entry. My names come from an Access database.

Here is the code that I'm using up to now, and it seems to bugg at the Save As line...

Debug.Print Company
Debug.Print Contract
Debug.Print Dateofcr
Dim FolderPath As String
Dim SpecFolder As String
Dim FileName As String
FolderPath = "G:\Scafa\Escalatn\Appendix 1 Builder\Test Folder\"
SpecFolder = Company & "\"
FileName = Company & Contract & Dateofcr & ".doc"
Debug.Print FolderPath & SpecFolder & FileName
ActiveDocument.SaveAs FileName:=FolderPath & SpecFolder & FileName


Here is the debug window (I had to filter out some information)

SOMECOMPANY
CONTRACT101
15 janvier
G:\Scafa\Escalatn\Appendix 1 Builder\Test Folder\SOMECOMPANY\SOMECOMPANYCONTRACT101 janvier.doc

[AyeSee]

Ok, i figured out the problem: the folder (ex: SAMECOMPANY) has to exist so that the code works.... Ideally, I would like for it to create the path if it does not already exist...

[AyeSee]

Annnd Success! (yay!)

I used an OLE object to check the existence of the folder path and create it if necessary


Set fs = CreateObject("Scripting.FileSystemObject")


' check to see if folder is created
If Not fs.folderexists(Path) Then
folders = Split(Path, "\")
For ifFolder1 = 0 To UBound(folders) - 1
folder = ""
For ifFolder2 = 0 To ifFolder1
folder = folder & folders(ifFolder2) & "\"
Next
If Not fs.folderexists(folder) Then
fs.createfolder folder
End If
Next
End If

[fumei]

"Annnd Success! (yay!)"

Good for you.

"Ok, i figured out the problem: the folder (ex: SAMECOMPANY) has to exist so that the code works.... "

My bolding. Did I not state:

"SaveAs is straight forward. It needs a valid path and filename."

and

"Remember what you pass as a filename MUST MUST MUST pass what Windows finds acceptable as a valid path and filename."

Obviously - or it should be obvious - if the folder does not exist, then it is NOT a valid path.

[AyeSee]

Yeah sorry about that, you did indeed warn me :P I was focused on the earlier recommendations you had given me...

I read your comment about users and error handling.... lol I can see it happening, they will find a way to enter some currupted sign somewhere and then I'll cry. lol

But for the scope of this solution, i think my Access Validation Rule should cover most probs ( & ( ) / \, etc.)

[fumei]

"lol I can see it happening, they will find a way to enter some currupted sign somewhere and then I'll cry. lol "

On one hand, probably they will. Just do the best you can, cover the basic possible errors and cross your fingers. You may want to add some kind of "I have NO idea what is going on" type error trap.