[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Copy data from Excel to CSV

Posted on 2011-10-14
Medium Priority
Last Modified: 2012-05-12
Good Evening Experts,

I need an Excel code that will pull the data from a workbook called "Labor Totals" and place it into a CSV or comma separated text file.

I'd like it to just loop through until it doesn't find any more rows as the number of rows changes.

If possible I'd also like for it to prompt the user for a file location / filename to create.

Best Regards,
Question by:RedstoneIT

Expert Comment

ID: 36972341
Are you wanting to do something different than the user just Saving As a .csv file?  Are you only wanting certain fields exported?  
LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 36972495
I assume you're building something off this base, or providing something that makes life easier than manually saving tabs as CSV's.  So, here goes.

The app in the attached workbook prompts the user for a SAVE AS location and filename, with defaults to .CSV.  Once selected, the Labor Total tab is COPIED to a new workbook, then saved at that path/filename, then closed.  A message box prompt confirms the save.

Here's the code (in a public module):
Sub exportToCSV()
Dim wkb As Workbook
Dim wks As Worksheet
Dim fileSaveName As Variant

    On Error GoTo errHandler
    Set wkb = ActiveWorkbook
    Set wks = wkb.Sheets("Labor Totals")
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="CSV (Comma Delimited) (*.csv), *.csv", Title:="Please select a folder and specify filename to save CSV file")
    If fileSaveName <> False Then
        Application.DisplayAlerts = False
        wks.Copy 'copies to a new workbook
        ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlCSV
        MsgBox "The Labor Totals tab was successfully saved as " & fileSaveName
    End If
    Exit Sub
    MsgBox "For some reason, you were missing the Labor Totals tab, or the file you selected had a problem  - at any rate, nothing was successfully saved" & Chr(10) & Chr(10) & "Err: " & Err.Number & " Err_Description: " & Err.Description, vbCritical, "Aborting..."
End Sub

Open in new window

See attached workbook.  There's a button on the demo sheet, Labor Totals for you to test.



Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

872 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