How to use VBA to save a file to desktop regardless of user?

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
Hello. I am trying to add this to a longer VBA code I have. I need two separate codes. Both of them I need Excel to save the active workbook with the file name (one being Pickorder1 and one being Pickorder2) along with today’s date. So save it to the Desktop regardless of user as this will be used by many. As CSV format. So the end result for one would be Pickorder1 31-Aug-2020.csv. Then similar with the other. So I just need the name of the file and today’s date. Thank you to anyone willing to help.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,630
See if this does the job.

VBA Code:
Sub seetest()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

ThisWorkbook.SaveAs Filename:=desktop & newfilename & " " & today

End Sub
 
Last edited:

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
See if this does the job.

VBA Code:
Sub seetest()

user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop"

End Sub
Thank you. So first off the user name is going to change. This file with the code will be sent around to others. Also this line of code wouldn’t save it correct?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,630
Try my edited post. Thought you just wanted the desktop part initially

Environ("Username") finds the users username
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT


Try my edited post. Thought you just wanted the desktop part initially

Environ("Username") finds the users username

So it saves it. However it keeps in the original name also. How can I name it exactly what I want in addition to the current date?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,630
Do you want to hard code the name?

VBA Code:
Sub seetest()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

ThisWorkbook.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Do you want to hard code the name?

VBA Code:
Sub seetest()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

ThisWorkbook.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub
Thank you this works. Last question. So this code will go into one workbook. Referenced as Checklist. However the workbook it will save is another one (titled Pickorder). I have a code that activates the open Pickorder workbook, however running this code after does not recognize that one has the active one. It saves the Checklist workbook (with the code built in). Is there a way change this code to identify the open workbook with a title such as Pickorder? Here is the activation code I have that doesn’t work with this. This activation code works in other codes by the way.



VBA Code:
    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Exit For
    End If
    Next w
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,630
To incorporate your code, try:

VBA Code:
Sub seetest()

Dim wb As Workbook
    
Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Set wb = ActiveWorkbook
    Exit For
    End If
    Next w

wb.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub
 
Solution

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
To incorporate your code, try:

VBA Code:
Sub seetest()

Dim wb As Workbook
   
Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Set wb = ActiveWorkbook
    Exit For
    End If
    Next w

wb.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub
It works perfectly!!! Thank you so much. It is very much appreciated. I was researching for awhile and couldn’t find anything like what I needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top