Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

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!
0
wilpitz
Asked:
wilpitz
  • 2
  • 2
1 Solution
 
FlysterCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now