Save excel macro file as .csv with date to same folder as original file (VBA)

[Jim885]

I'm trying to have an excel VBA file create and save a .csv file. The newly created .cvs file should be named after the tab name, and include the current date as the file name. The new .csv should also be saved into the same directory as the VBA file that created it.

I'm putting pieces of code together, but not having much success. Here's what I got so far, but it doesn't work, so I need someone to straighten this out.

Ideally, I'd like for the CVS file to be saved and closed during this process, while leaving the VBA file visible.

Thanks for any assistance.

Sub SaveAsCSV()
Application.DisplayAlerts = False
    Dim Name As String
    Name = ThisWorkbook.Path & "\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm.dd.yy") & ".csv"    ' I also tried this--->  & FileFormat:=xlCSV, CreateBackup:=False  (but it doesnt work) 
Application.DisplayAlerts = True
  MsgBox "File " & Name & " has been Created and Saved as:  " & Name, , "Copy & Save Report"
End Sub

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] SaveAsCSV()
    
    [color=darkblue]Dim[/color] strName [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    strName = ThisWorkbook.Path & "\" & ActiveSheet.Name & " " & Format(Date, "mm.dd.yy") & ".csv"
    
    ActiveSheet.Copy    [color=green]'copy the sheet as a new workbook[/color]
    ActiveWorkbook.SaveAs Filename:=strName, FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=[color=darkblue]False[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "File has been Created and Saved as:  " & vbCr & strName, , "Copy & Save Report"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Jim885]

Thanks very much AlphaFrog. That worked perfectly, and is a lot neater than what I was working on whlie you nailed it in less than 20 minutes. That just blows me away!

Here's what I came up with. While it works, it still couldn't keep the original file open.

Sub SaveAsCSV()
Application.DisplayAlerts = False
    Dim Name As String
    Dim FileName As String
    Name = ActiveSheet.Name & Format(Now(), "mm.dd.yyyy")
    FileName = ThisWorkbook.Path & "\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm.dd.yyyy") & ".csv"
   
   ActiveWorkbook.SaveAs FileName:= _
        ThisWorkbook.Path & "\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm.dd.yyyy") & ".csv", FileFormat:=xlCSV, _
        CreateBackup:=False
    
  MsgBox "File " & Name & " has been Created and Saved under:  " & FileName, , "Copy & Save Report"
  ActiveWorkbook.Close
  Application.DisplayAlerts = True
End Sub

So how much longer did it take me to get close to what I needed, while still having an issue, and the code being larger? ... 45 minutes longer than it took you.

Not to mention that I worked on it for more than an hour (much more) before asking for help.

You are great. Thanks!

I need to practice more ................

 

[AlphaFrog]

You're welcome.

You did a pretty good job. You were really 90% there. Keep working at it. The more you code, the better you'll get.

 

[Pinaceous]

How can this code be improvised in saving it as an excel macro file as .xlsm with date to same folder as original file (VBA)??