Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Export Selection as CSV

    I am running Excel 2010 on Windows 7.

    I have to exchange data from Excel to a program that doesn't play nice ... it only wants CSV. I will be doing this repeatedly as the data updates.

    How do I automate saving a named range from a worksheet to a file in CSV?

    I have the following macro that exports named ranges to an HTML file on my desktop. I am thinking that modifying the bold code will work, but I am not sure.

    Code:
    Sub exportToHTML()
    '
        Dim strMyDocs As String
        strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desktop")
        
        With ActiveWorkbook.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            Filename:=strMyDocs & "\page1.htm", _
            Source:="grades_1", _
            HtmlType:=xlHtmlStatic)
            .Publish (False)
            .AutoRepublish = False
       End With
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about something like this (change the named range and filename as appropriate...)

    Code:
    Option Explicit
    Sub ExportRangeToCSV()
      Dim wbkNew As Workbook
      Dim strMyDocs As String
      strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desktop")
          
      Set wbkNew = Workbooks.Add
      With wbkNew
        ThisWorkbook.Names("MyNamedRange").RefersToRange.Copy .Worksheets(1).Range("A1")
        .SaveAs Filename:=strMyDocs & "\NewName.csv", _
            FileFormat:=xlCSVMSDOS
        .Close (False)
      End With
    End Sub
    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    boobounder (2011-09-23)

  4. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Very cool. Thanks.

    EOM

  5. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Formula to Value Prior to Export

    The code below is the solution to my problem.

    Now ... I want to do one more thing. I'd like all the formulas in the range to be converted to values when exported, while retaining the formulas in the spreadsheet.

    The reason is that an external program dumb enough to only accept a CSV also doesn't have any facility for formulas.

    Code:
    Option Explicit Sub ExportRangeToCSV()   Dim wbkNew As Workbook   Dim strMyDocs As String   strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desktop")          Set wbkNew = Workbooks.Add   With wbkNew     ThisWorkbook.Names("MyNamedRange").RefersToRange.Copy .Worksheets(1).Range("A1")     .SaveAs Filename:=strMyDocs & "\NewName.csv", _         FileFormat:=xlCSVMSDOS     .Close (False)   End With End Sub
    Last edited by Deadeye81; 2011-09-23 at 19:49. Reason: merged threads

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Regarding your question, change the appropriate lines to:
    ThisWorkbook.Names("MyNamedRange").RefersToRange.C opy
    .Worksheets(1).Range("A1").PasteSpecial Paste:=xlValues

    Steve
    Last edited by Deadeye81; 2011-09-23 at 19:50. Reason: merged threads

  7. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    I'm not getting this to work.

    As soon as I finish adding the word PasteSpecial I get a tooltip that look like this Copy([Destination]). That tooltip persist as I continue typing the rest of the new line Paste:=xlValues. To me this suggests that it is never "satisfied" that I've entered the right arguments.

    When I save and run the macro, the debugger identifies the whole line as the problem, but I'm not sure why. Code below:

    Code:
    Option Explicit
    Sub ExportRangeToCSVwithoutFormulas()
      Dim wbkNew As Workbook
      Dim strMyDocs As String
      strMyDocs = CreateObject("WScript.Shell").SpecialFolders("Desktop")
          
      Set wbkNew = Workbooks.Add
      With wbkNew
        ThisWorkbook.Names("CSV_Export_3").RefersToRange.Copy .Worksheets(1).Range("A1").PasteSpecial Paste:=xlValues
        .SaveAs Filename:=strMyDocs & "\NewName.csv", _
            FileFormat:=xlCSVMSDOS
        .Close (False)
      End With
    End Sub

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In the original it was 2 lines since it was a copy/paste. The new code is 2 lines. The first copies then 2nd pastespecials:

    ThisWorkbook.Names("CSV_Export_3").RefersToRange.C opy
    .Worksheets(1).Range("A1").PasteSpecial Paste:=xlValues

    Steve

  9. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Alrighty then ...

    It works ... but can you tell me why it has to be on 2 lines? Is there a character limit or something?

    Other than that, I'm good to go.

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by boobounder View Post
    Alrighty then ...

    It works ... but can you tell me why it has to be on 2 lines? Is there a character limit or something?

    Other than that, I'm good to go.
    Boobounder,

    Two separate lines because they are two separate commands/operations.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •