I broke Save As

[iwilli]

Still learning. I'm trying to create a Macro that several different people will use on different computers. The problem is if I use the "Save As" then it saves the files to my user. This causes the macro not to work on anyone's computer other than mine. What am i doing wrong?

Sub EAR_UPS_CONTACT_IMPORT()
'
' EAR_UPS_CONTACT_IMPORT Macro
' Creates the .csv file to upload to UPS for EARs
'

'
    Sheets("CSEXPORT").Select
    Cells.Select
    Selection.Value = Selection.Value
    Range("A1").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\iwilli\Desktop\EAR_UPS_Import.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
End Sub

[AlvaroSiza]

You are specifying a static location in the 'Filename:=' argument. Would you mind asking the user where they would like it saved via dialog?

[iwilli]

I would prefer to have the user choose a location. I just don't know how to do that.

[JosephP]

if you want to just save to the desktop then you can get that path with:

CreateObject("WScript.Shell").SpecialFolders.Item("Desktop")

[iwilli]

AlvaroSiza
I would prefer to have the user choose a location. I just don't know how to do that.

[jeffreybrown]

Cross post

http://www.mrexcel.com/forum/showthread.php?t=634252

[JosephP]

dim sPath as string
spath = application.getsaveasfilename
ActiveWorkbook.SaveAs Filename:=spath & "\EAR_UPS_Import.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False

[iwilli]

This gets the path name but when it goes to actually save the file I get this error message.
"EAR_UPS_Import.csv' cannot be accessed. The file may be corrupted, located on a server that is not responding or read-only."
What am I doing wrong?

[iwilli]

Thank you for your help. This is perfect. More than what I was looking for.

[iwilli]

So this is finally what worked. Thanks for your help I could have figured it out otherwise.

Sub EAR_UPS_CONTACT_IMPORT()
'
' EAR_UPS_CONTACT_IMPORT Macro
' Creates the .csv file to upload to UPS for EARs
'

'
    Sheets("CSEXPORT").Select
'    Cells.Select
'    Selection.Value = Selection.Value
Dim sPath As String
sPath = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=sPath _
        , FileFormat:=xlCSV, CreateBackup:=False
End Sub

[JosephP]

oh yeah - my bad. glad you figured out the right syntax!

[iwilli]

I finally got my solution

MyMonth = Format(DateTime.Now, "mm")
    MyYear = Year(Date)
    MyWeek = Format(DateTime.Now, "ww")
    MyDay = Format(DateTime.Now, "dd")
    MyFileName = "Week " & MyWeek & " Condensed_Weekly_Advisor_Results_by_Region-" & MyMonth & "-" & MyDay & "-" & MyYear & "_Bulletin"
    ActiveWorkbook.SaveAs Filename:=UserName & "\Documents\Region\Bulletin\2012\" & MyFileName, FileFormat:=51