Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-17
5
Medium Priority
?
76 Views
Last Modified: 2015-02-19
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
Comment
Question by:wilpitz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 40615574
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40618618
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
 

Author Comment

by:wilpitz
ID: 40618624
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40618630
Just add:

ActiveWorkbook.Close
0
 

Author Closing Comment

by:wilpitz
ID: 40618843
Used  ActiveWorkbook.Close Savechanges:=False at the end to close the sheet, works GREAT!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question