Variable Referencing for Workbook Names and File Paths VBA

[turner38]

Hello All,

I am trying to make my reference for "August.xlsm" so that if it was renamed (i.e. "August 8-15-11") the code would still know to reference that workbook. Is this possible?

Also I have the same issue in the next part of my code. I want to save the "September.xls" workbook to "My Documents" for which ever user has the file. So if the user was not Jim Lewis it would still save the workbook to the current user's "My Documents". Would this be possible?

    Workbooks.Add
    Sheets("Sheet1").Name = "Data"
    Workbooks("August.xlsm").Sheets("Data").Range("$A$1:$O$50000").Copy
    ActiveWorkbook.Sheets("Data").Range("$A$1:$O$50000").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
 
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Jim Lewis\My Documents\September.xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

Thanks,

 

[andiemac]

You should be able to swap out user name with:
Application.UserName

"C:\Documents and Settings\" & Application.UserName & "\My Documents\September.xls", FileFormat:= _


And referencing the file you are actively in can be done with:
ActiveWorkbook

ActiveWorkbook.Sheets("Data").Range("$A$1:$O$50000").Copy

You could also create a dynamic name for the save as file name...

For example if you want to name it by the name of what ever month is the month after the month you are in:
FileMoName = Format(DateSerial(Year(Now()), Month(Now()) + 1, 1), "Mmmm")

"C:\Documents and Settings\" & Application.UserName & "\My Documents\" & FileMoName& ".xls", FileFormat:= _

 

[turner38]

Thanks. The "Application.Username" gives me the author of the excel file, but I am needing the Windows Username. This seems to work:

UNameWindows = Environ("USERNAME")
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\" & UNameWindows & "\Desktop\September.xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False