excelvbasaveattrenviron

Editing an automated naming macro in excel (based on PC name and date, with variable save path)

I'm currently trying to get an excel file to save into YYYYMMDD_fixed name piece_INITIALS OF LAST PERSON TO EDIT.

I'm using Environ function to call the User's and PC's name in a cell that i've found can be used to add to the name.

The issues i'm trying to fix are:

  1. how can i define the save path to work on any PC regardless of user name, as current path has Users/my name/ , and up to 4 people with different PCs and names will edit this file. it should just save on Desktop on any of the 4 PCs

  2. how can i modify the 

strFile = "C:\Users\me\Desktop\" & Format(dtDate, "ddmmyyyy") & ".xlsx"

part so that it displays YYYYMMDD_name (i get this part ok) _ABC where ABC value is in cell A1 generated by the below attr function?

the function used is 

Function attr(choice) As String
   Select Case (choice)
      Case "computer": attr = Environ("Computername")
      Case "user": attr = Environ("UserName")
   End Select
End Function

and the one i use to save (albeit a different format on a different file) is

Dim dtDate As Date
    dtDate = Date

    Dim strFile As String
    strFile = "C:\Users\me\Desktop\" & Format(dtDate, "ddmmyyyy") & ".xlsx"

    ActiveWorkbook.SaveAs Filename:=strFile, FileFormat _
    :=51, CreateBackup:=False

Any help would be greatly appreciated! Programming is not my main job, just trying to automate bits where possible, so go easy on me please :)

Solution

  • Maybe something like that will help:

    Dim strFile As String, strUserName As String
Dim dtDate As Date

dtDate = Now
strUserName = attr("user")

strFile = "C:\Users\" & strUserName & "\Desktop\" & Format(dtDate, "ddmmyyyy") & "_" & Sheets("Sheet1").Range("A1").Value & ".xlsx"
MsgBox strFile

    Note that I assigned the value of an active username to strUserName and I'm using it with your strFile. I also added Sheets("Sheet1").Range("A1").Value to the code (change sheet name accordingly). The final result will look like that:

    C:\Users\username\Desktop\12082019_username.xlsx