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

 

[shg]

Neighbor's on XP, you're on W7?

 

[iwilli]

Here is a newbies response. "How did you know that?" no seriously how did you know that? and is there a way around it?

 

[shg]

A W7 user's desktop is C:\Users\Bob\Desktop

And XP users's desktop is C:\Documents and Settings\Bob\Desktop

Where do you wnat the file saved -- always on the users's desktop?

 

[iwilli]

I was going for the simplest place it can be saved. The .csv file will be used right away and then never needed again.

 

[jeffreybrown]

Cross post

http://www.excelforum.com/excel-programming/829841-i-broke-save-as.html

 

[iwilli]

This is finally what had to be done to solve it.

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

and when it asks for the path name I have to type the .csv and it works.

 

[iwilli]

Thank you everyone for the help! I couldn't do it without y'alls help.