Change Macro path for any user of company

[YannickDVTC]

Hi all, 

I am currently working with a macro which export a sheet as a CSV to file (shared with all the company thanks to onedrive). The macro is working, but as I have to share the file with collegues, I tried to change the path to allow any user to send the CSV to the onedrive file, but I doesn't work. 
Here is the macro : 

"Sub CSVExport()
Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

'Dim Change below to "- 4" to become compatible with .xls files
MyFileName = "C:\Users\ydvog\Template Article Creation\CSV Supplier\Article Creation_" & _
Range("GA2") & _
Format(Date, "_ddmmyy_") & _
Format(Time, "hmmss") & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub"

I tried to changed \ydvog\ (which is me) to \%userprofile%\, but I am getting an error on this line : 

TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True

Note that I am not good at all with VBA, just looking for VBA's on internet, trying to understand them and rearrange them to fit my purposes. 

If anyone could give my insight about that, it would be appreciated. 

Thanks a lot, 

Yannick

[#2]

@YannickDVTC this is the code I use for this situation (refer to lines 14,15, & 18 for the syntax).   

Hopefully this will work for you.   

Sub CSVExport()
Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

Set ObjWshNw = CreateObject("WScript.Network")
CurUser = ObjWshNw.UserName

'Dim Change below to "- 4" to become compatible with .xls files
MyFileName = "C:\Users\" & CurUser & "\Template Article Creation\CSV Supplier\Article Creation_" & _
Range("GA2") & _
Format(Date, "_ddmmyy_") & _
Format(Time, "hmmss") & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub