使用pastespecial将excel数据复制到csv中
问题描述:
我需要从我的excel表格导出数据到csv。我有这样的代码。但我的Excel数据中的一些列中有公式。所以,出口到“csv”时,我得到的是“0”而不是价值。使用pastespecial将excel数据复制到csv中
是否有对csv的pastespecial支持?!或者以任何其他方式将单元格值导出为csv。
我当前的代码是:
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
答
尝试PasteSpecial
方法。
见的资源在这里MSDN: https://msdn.microsoft.com/en-us/library/office/ff839476.aspx
另请参见这里: Excel VBA Copy Paste Values only(xlPasteValues)
你可能也想尝试: ActiveSheet.PasteSpecial xlPasteValues
像这样:
'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
答
我测试这个,它不会生成吃了一个错误。我认为你可能会遇到的问题是,Excel如何处理与从头开始创建的新工作簿的交互,然后粘贴到该工作簿上。在这种情况下,我只创建了一个新工作表,将数据复制到新工作表,然后将工作表移动到其自己的工作簿实例中。然后将新实例保存在所需的CSV中并关闭。
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
尝试用'ActiveSheet.Range( “A1”)。PasteSpecial的xlPasteValues' – Slai