Excel VBA filename containing a cell value and the user name

Question

i have a master.xlsx sheet where i fill in multiple columns. I would like to export the column E to a .csv file.

It's working as it should although i'd like the filename to be in the following format:

DATE-USERNAME-FIXEDTEXT-ValueFromTheCellD2(in master.xlsx).csv

So far I have I have manage this macro to create the .csv, it does contain the column E from master.xlsx, all good. The struggle however is with the filename:

Sub generatecsv()
'
' generatecsv Macro
'

'
    Columns("E:E").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\JONDOE\Documents\FixedText_" _
    & Format(Now(), "DD-MMM-YYYY") & ".csv"
            Windows("Miluna_1.1.xlsm").Activate
End Sub

My macro will create a filename FixedText_24-Feb-2023.csv

I can work myself on the order of the variables in the filename, but i dont understand how to get the username, since this file will be used by multiple users; and also how to get the D2 value from master.xlsx so it can be used in the final .csv filename.

To get a username i know here is the Environ("Username") function, but i dont understand the usage.

Can i put everything in the ActiveWorkbook.SaveAs Filename or do i need to create variables for the username, the D2 value, fixedtext and the date? And just refer to these variables when specifying ActiveWorkbook.SaveAs Filename?

Thank you very much for any pointers

Regarding the user name, this example could help: https://stackoverflow.com/a/8988438/17017616 — leosch, Feb 24 '23 at 16:50
`FilePath = Environ("USERPROFILE") & "\Documents\" & Format(Now(), "DD-MMM-YYYY") & "-" & Environ("USERNAME") & "-" & FIXEDTEXT & "-" & Workbooks("Master.xlsx").Sheets("Sheet1").Range("D2").Value & ".csv"`. It's quite long, so maybe split it into a few variables e.g. `FolderPath`, `NameDate`, `UserName`, `FixedText`, `Filename`... — VBasic2008, Feb 24 '23 at 17:50

jacouh · Accepted Answer · 2023-02-24T20:33:48.837

This is a simple job to do, refer to the comments of @VBasic2008 above and what you've done.


Sub generatecsv()
    Dim d2Value

    d2Value = ActiveSheet.Range("D2").Value

    Columns("E:E").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:=Environ("USERPROFILE") & "\Documents\FixedText_" _
      & Environ("USERNAME") _
      & "-" & Format(Now(), "DD-MMM-YYYY") _
      & "-" & d2Value & ".csv", FileFormat:=xlCSV

    ActiveWorkbook.Close

    Windows("Miluna_1.1.xlsm").Activate
End Sub

By adding 2 points:

FileFormat:=xlCSV to save as CSV text, by default it's a binary Excel doc.
ActiveWorkbook.Close to close the created csv Sheet.

Excel VBA filename containing a cell value and the user name

1 Answers1