CODEWITHSUNDEEP
vbaexcelcsvexcel-formula
SanjChau
SanjChau

Reputation: 21

copying excel data into csv using pastespecial

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

Upvotes: 0

Views: 3879

Answers (2)

viper941
viper941

Reputation: 156

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

Upvotes: 0

GisMofx
GisMofx

Reputation: 1014

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

Upvotes: 2

Related Questions