Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • 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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
Just add:

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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