copying excel data into csv using pastespecial

Question

I need to export data from my excel sheet into a csv. The code that i have does that. But some of the columns in my excel data have formulae in them. So, when exporting to "csv", i am getting "0" instead of the value.

Is there a pastespecial support for csv ?! Or any other way to export cell-values into csv.

My current code is :

Sub submit_task()
'
' submit_task Macro
'

'
Dim Filename As String
Dim WB As Workbook

Application.DisplayAlerts = False

Filename = "Job_Details.csv"

'Copy the contents of required sheet ready to paste into the new CSV
Sheets("output_sheet").Range("A3:C4").Copy 'Define your own range

'Open a new XLS workbook, save it as the file name
Set WB = Workbooks.Add
With WB
    .Title = "Job Details"
    .Subject = "Task Submitted"
    .Sheets(1).Select
    ActiveSheet.Paste
    .SaveAs "C:\UI\" & Filename, xlCSV
    .Close
End With

Application.DisplayAlerts = True

End Sub

try replacing `ActiveSheet.Paste` with `ActiveSheet.Range("A1").PasteSpecial xlPasteValues` — Slai, Aug 04 '16 at 01:16

score 2 · Answer 1 · edited May 23 '17 at 12:33

2

Try the PasteSpecial method.

See the resources here on MSDN: https://msdn.microsoft.com/en-us/library/office/ff839476.aspx

See Also here: Excel VBA Copy Paste Values only( xlPasteValues )

You probably want to try: ActiveSheet.PasteSpecial xlPasteValues

Like this:

'Open a new XLS workbook, save it as the file name
Set WB = Workbooks.Add
With WB
    .Title = "Job Details"
    .Subject = "Task Submitted"
    .Sheets(1).PasteSpecial xlPasteValues
    .SaveAs "C:\UI\" & Filename, xlCSV
    .Close
End With

edited May 23 '17 at 12:33

Community

1
1

answered Aug 02 '16 at 16:31

GisMofx

982
9
27

I tried that already. For pasteSpecial, i get an error that "PasteSpecial method of Worksheet Class failed". – SanjChau Aug 02 '16 at 16:57
@SanjChau I've added more sample code for you to try – GisMofx Aug 02 '16 at 17:03

viper941 · Answer 2 · 2016-08-03T11:09:09.520

I tested this and it does not generate an error. I think the issue you may be running into is how excel is handling interaction with a new workbook created from scratch, and then pasting to that. In this case I just created a new sheet, copied the data to that new sheet, and then moved the sheet into its own workbook instance. The new instance is then saved at the desired CSV and closed.

Sub Create_CSV()
  Application.DisplayAlerts = False
'Select & copy data for CSV
  Range("A3:C4").Select
  Selection.Copy
'Add a new sheet to become your csv and paste data
  Sheets.Add After:=ActiveSheet
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
  Application.CutCopyMode = False
'Move the sheet into it's own instance
  ActiveSheet.Move
'Rename the tab
  ActiveSheet.Name = "Task Submitted"
'Save the WB as a CSV and close
  ActiveWorkbook.SaveAs Filename:="C:\UI\Job_Details.csv", FileFormat:= _
    xlCSVMSDOS, CreateBackup:=False
  ThisWorkbook.Saved = True
  ActiveWindow.Close
  Application.DisplayAlerts = True
End Sub

copying excel data into csv using pastespecial

2 Answers2