Excel Macro export populated records to CSV file with time and date as name

I need a macro that will export all data from A3 to G3 all the way down to the last record with data and export this to C:\temp. The name of file needs to be the date in yyyymmddhmmss

Any help would be GREAT!
wilpitzAsked:
Who is Participating?
 
FlysterConnect With a Mentor Commented:
This should give you what you're looking for:
Sub SaveAsCSV()
Dim fn As String
Dim i As Integer

i = Application.ActiveSheet.Cells.SpecialCells(xlLastCell).Row
fn = Format(Now(), "yyyymmddhmmss")
  Range("A3:G" & i).Select
    Selection.Copy
      Workbooks.Add
      ActiveSheet.Range("A3").Select
      ActiveSheet.Paste
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Temp\" & fn & ".csv", FileFormat:=xlCSVMSDOS _
        , CreateBackup:=False
    ActiveWorkbook.Save
        
End Sub

Open in new window

Flyster
0
 
Rob HensonFinance AnalystCommented:
Is there data beyond column G that you don't want copying?

If not then you can just copy the sheet and save as csv, rather than creating a new sheet and copying and pasting data to it. Copying some of Flyster's suggestion:

Sheets("Sheet1").Copy
fn = Format(Now(), "yyyymmddhmmss")
ActiveWorkbook.SaveAs Filename:="C:\Temp\" & fn & ".csv", FileFormat:=xlCSV, _
        CreateBackup:=False

Open in new window


Thanks
Rob
0
 
wilpitzAuthor Commented:
Is there a way to close the new sheet once it is saved automaically.  I tried a couple of ways and I could not get it to work.

The rest of the code works great!! Will award points for above.
0
 
Rob HensonFinance AnalystCommented:
Just add:

ActiveWorkbook.Close
0
 
wilpitzAuthor Commented:
Used  ActiveWorkbook.Close Savechanges:=False at the end to close the sheet, works GREAT!
0
All Courses

From novice to tech pro — start learning today.