VBA SaveCopyAs with a file path from a variable

Question

I'm not really good in VBA and I would like to know if someone can help me. There is my problem.

I wan't to save as a workbook to a folder but depending of the user, the path changes. So, to solve this issue, I have created this code:

Public Function MyDocsPathS() As String
    MyDocsPathS = Environ$("USERPROFILE") & "\" & "OneDrive\Soumission"
End Function

This works well. However I don't know how to write it in the SaveCopyAs function. I have tried this and other ways without any success: (See section between**)

Sub Soumission()
    Sheets("Modèle Soumission").Visible = True
    **ActiveWorkbook.SaveCopyAs "MyDocsPathS & \S0000x.xlsm"**
    Sheets("Modèle Soumission").Visible = False
    Workbooks.Open (MyDocsPathS & ("S0000x") & ".xlsm")
End Sub

Thanks for any help!

score 3 · Answer 1 · answered Mar 06 '17 at 02:47

3

Your code should look like this:

Public Function MyDocsPathS() As String
    MyDocsPathS = Environ$("USERPROFILE") & "\" & "OneDrive\Soumission"
End Function

Sub Soumission()
    Sheets("Modèle Soumission").Visible = True
    ActiveWorkbook.SaveCopyAs MyDocsPathS() & "\S0000x.xlsm"
    Sheets("Modèle Soumission").Visible = False
    Workbooks.Open MyDocsPathS() & "\S0000x.xlsm"
End Sub

Theoretically, you can use either MyDocsPathS() & "\S0000x.xlsm" or just MyDocsPathS & "\S0000x.xlsm" (i.e. without the ()) but I believe it is good practice to have the brackets there to make it clearer that it is a function that is being called, and not just a variable.

answered Mar 06 '17 at 02:47

YowE3K

23,852
7
26
40

@YowE3K what is the point of `Function MyDocsPathS` ? it gets no variable, nor does it do any special calculation. You could just declare a variable named `MyDocsPathS` that gets the value of `Environ$("USERPROFILE") & "\" & "OneDrive\Soumission"` – Shai Rado Mar 06 '17 at 03:45
1

@ShaiRado Personally, I would have used a global variable in my own code and initialised it at the start of my main code. But there are advantages (code reusability-wise) in having a function and that is the way the OP has chosen to go. Explaining how to do it in the way I prefer to do things was beyond the scope of the OP's current question. – YowE3K Mar 06 '17 at 04:01
@YowE3K take a look here http://stackoverflow.com/questions/42609419/use-excel-vba-msxml2-xmlhttp-object-to-update-sharepoint-list are you familiar working with `MSXML2.XMLHTTP` object ? – Shai Rado Mar 06 '17 at 04:15
1

@ShaiRado - Sorry, I may have used it once, perhaps twice, in my life. (More likely never - I don't remember anything I would have used it for.) I've looked through the code to see if anything obvious stuck out to me (sometimes the obvious things get overlooked by experts until someone who knows nothing about it says "hey - does such-and-such make sense?") but I didn't see anything. – YowE3K Mar 06 '17 at 06:31

VBA SaveCopyAs with a file path from a variable

1 Answers1