我有一个脚本任务,该任务打开了excel文件并将其保存为.csv,但它保存了用文件打开的表,而我想选择要保存为a的表格。CSV,这是我目前正在使用的脚本:
Public Sub Main()
Dim oMissing As Object = System.Reflection.Missing.Value
Dim xl As New Microsoft.Office.Interop.Excel.ApplicationClass()
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim laPath As String = "C:Filename.xlsx"
xlBook = DirectCast(xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing), Workbook)
xl.DisplayAlerts = False
xlSheet = DirectCast(xlBook.Worksheets.Item(1), Worksheet)
xlBook.SaveAs("C:Filename.csv", 6, oMissing, oMissing, oMissing, oMissing,, oMissing, oMissing, oMissing, oMissing, oMissing)
xl.Application.Workbooks.Close()
Dts.TaskResult = ScriptResults.Success
End Sub
我认为我必须使用xlbook.Worksheets("Sheet1").Activate
,但我似乎找不到它。
您必须按名称打开工作表,然后使用Select()
函数选择它:
xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Select()
您的代码将如下:
Public Sub Main()
Dim oMissing As Object = System.Reflection.Missing.Value
Dim xl As New Microsoft.Office.Interop.Excel.ApplicationClass()
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim laPath As String = "C:Filename.xlsx"
xlBook = xl.Workbooks.Open(laPath)
xl.DisplayAlerts = False
xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Select()
xlBook.SaveAs("C:Filename.csv", Excel.XlFileFormat.xlCSV,Excel.XlSaveAsAccessMode.xlNoChange)
xl.Application.Workbooks.Close()
Dts.TaskResult = ScriptResults.Success
End Sub