Solved Saving an excel workbook to a specific location using macro

February 5, 2013 at 14:52:36
Specs: Windows 7

Im trying to save a excel workbook to the desktop icin. For background im have the workbook with which is macro enabled. Im distributing this file to several computers in the workplace. The point is hen they run the macro it saves automactally to the the desktop icon on their computer. The problem im having now is i can create a icon but when the macro runs and tries to save it creates a copy of itself in "My Documents" and then ask the user to override the current file. Its frustrating because i then have to create a copy under a different name delete the old one and it will work but the problem repeats if i close the workbook out.

The save language im using is:


See More: Saving an excel workbook to a specific location using macro

Report •

February 6, 2013 at 08:14:30

Can you post the full macro so we can see what's going on?

Report •

February 6, 2013 at 09:47:39

Application.ScreenUpdating = False

'Calculator   Archive Col
'D10            B
'D11            C
'E14            D
'E18            E
'319            F
'D9             A

    If Range("D10") = "0" Then
    Resp1 = MsgBox("You have not entered the Part Size. Please do so, press 'OK' now, then continue.", vbOKOnly)
ElseIf Range("D11") = "0" Then
    Resp1 = MsgBox("You have not entered the First Stamp Length. Please do so, press 'OK' now, then continue.", vbOKCancel)
    ElseIf Range("D9") = "0" Then
    Resp1 = MsgBox("You have not entered the Part Number. Please do so, press 'OK' now, then continue.", vbOKCancel)
End If



LastRow = Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Row + 1 'Sets the last row in the Archives

'Copies the Calculator data to the Archive
        Sheets("Archive").Range("B" & LastRow) = Range("D10")
        Sheets("Archive").Range("C" & LastRow) = Range("D11")
        Sheets("Archive").Range("D" & LastRow) = Range("E14")
        Sheets("Archive").Range("E" & LastRow) = Range("E18")
        Sheets("Archive").Range("F" & LastRow) = Range("E19")
        Sheets("Archive").Range("A" & LastRow) = Range("D9")
'Date and time of the archiving
        Sheets("Archive").Range("G" & LastRow) = Now

   'Clears out the input data in the calculator but does not touch the calculated cells in the calculator
        Range("D9,D10,E14,D11").Value = "0"
'Sort on Column A

Sheets("Archive").Visible = True

Sheets("Archive").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Report •

February 6, 2013 at 14:37:20
✔ Best Answer

I'm not sure what you mean by "save a excel workbook to the desktop icon " Are you trying to save to the Desktop?

If so, you can try this code instead of the Save statement you have...

        sUser = Environ("Userprofile")
        sDir = "\Desktop\"
        sFname = "YOURFILENAME.xlsm"
        sName = sUser & sDir & sFname
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=sName

Substitute the file name where indicated.

A couple of points to be aware of ....
the ENVIRON function works on a normal Windows environment which could be different on a company network environment. So you will need to test this first to see what the

sUser = Environ("Userprofile")


Application.DisplayAlerts = False
will suppress the prompt to save over an existing file. Test without that statement to ensure that the file is being saved to the correct place.

Report •

Related Solutions

February 7, 2013 at 12:51:37

Okay I will give this a try now. Also where im putting this is on computers that are not hoooked up to a company network. I still have to but I will also try it out on them.

Report •

February 7, 2013 at 14:07:02

So the code works and saves everything to the desktop, but it also will make a copy of itself in My documents. Then when i go to open it after i closed it out it will only come up [Read-Only] and says it is locked by me. I took out all the protection for both sheest to see if that would make a difference it didnt though. Also i made this in Excel 2010 and had to convert to Excel 2003 when i transfered with a USB. Should i Save it on 2010 as a different type before transfering?

Report •

February 7, 2013 at 16:20:21

I can't replicate the problem and I can't think of anything within native excel that would do that. Some things to try ...

Are there any other macros running that might be saving it to Documents?

It could be that your network administrator has set up some routine to automatically save everything to the Documents folder. Speak to your administrator and ask if that may be happening and how to avoid it.

Report •

Ask Question