Export Selection as CSV

Topic

New Reply

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 & “[B]page1.htm[/B]“, _
        Source:=”[B]grades_1[/B]“, _
        [B]HtmlType:=xlHtmlStatic[/B])
        .Publish (False)
        .AutoRepublish = False
   End With
End Sub

Reply | Quote

Replies

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

Reply | Quote

Very cool. Thanks.

EOM

Reply | Quote

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

Reply | Quote

Regarding your question, change the appropriate lines to:
ThisWorkbook.Names(“MyNamedRange”).RefersToRange.Copy
.Worksheets(1).Range(“A1”).PasteSpecial Paste:=xlValues

Steve

Reply | Quote

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

Reply | Quote

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.Copy
.Worksheets(1).Range(“A1”).PasteSpecial Paste:=xlValues

Steve

Reply | Quote

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.

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote