rename file including cell value

[neveu]

hello,
i would like to rename a file using the information from a cell: I1 from worksheet "Buchung"
the final file should be [info from I1] & ["output_1"]&[.mac]
could you please help me to edit it?
thank you

here is my existing code:

Sub text_saveas_mac_extensions()
'
' Saves the codingdata spreadsheet as text file
'
'
    Sheets("codingdata").Select
    Sheets("codingdata").Copy ' copies to coding spreadsheet
    filelocation = "C:\Documents and Settings\730001108\Desktop\Stapples\AR GE Hamburg - Alexandru Balanescu\" 'sets the location where the file should be saved
    ActiveWorkbook.SaveAs Filename:=filelocation & Format(Date, "dd-mm-yy") & " output_1" & ".txt", FileFormat:=xlTextMSDOS, CreateBackup:=False ' saves file as text file
On Error GoTo Err1
ActiveWorkbook.Close False 'closes the file
oldfilename = filelocation & Format(Date, "dd-mm-yy") & " output_1" & ".txt"
newfilename = filelocation & Format(Date, "dd-mm-yy") & " output_1" & ".mac"
Name oldfilename As newfilename 'changes the file extension from txt to mac
MsgBox ("File Saved As " & " output_1" & ".mac") ' message box confirming the saving of the file and the name under which it has been saved
Err1:  MsgBox ("you've already generated the file for today")
retval = Shell("explorer.EXE " & filelocation, vbNormalFocus) ' open the explorer folder where the file has been saved
Sheets("Buchung").Select
End Sub

 

[wigi]

For instance:

Sub text_saveas_mac_extensions()
'
' Saves the codingdata spreadsheet as text file
'

    Sheets("codingdata").Copy    ' copies to coding spreadsheet
    filelocation = "C:\Documents and Settings\730001108\Desktop\Stapples\AR GE Hamburg - Alexandru Balanescu\"    'sets the location where the file should be saved
    ActiveWorkbook.SaveAs Filename:=filelocation & "temp.txt", FileFormat:=xlTextMSDOS, CreateBackup:=False    ' saves file as text file
    On Error GoTo Err1
    ActiveWorkbook.Close False    'closes the file
    oldfilename = filelocation & "temp.txt"
    newfilename = filelocation & Range("I1").Text & " - " & Format(Date, "dd-mm-yy") & " output_1" & ".mac"
    Name oldfilename As newfilename    'changes the file extension from txt to mac
    MsgBox "File Saved As " & " output_1" & ".mac"    ' message box confirming the saving of the file and the name under which it has been saved
Err1:     MsgBox "you've already generated the file for today"
    retval = Shell("explorer.EXE " & filelocation, vbNormalFocus)    ' open the explorer folder where the file has been saved
    Sheets("Buchung").Select
End Sub

Wigi

 

[neveu]

thank you very much