Copy data from Excel to CSV

Posted on 2011-10-14
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
    LVL 4

    Expert Comment

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

    Accepted Solution

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now