在SSIS脚本任务中激活特定的Excel工作表

Question

我有一个脚本任务，它打开一个Excel文件并将其保存为.csv格式，但它保存的是与文件一起打开的工作表，而我想选择要保存为.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，但我似乎找不到它。

- JamesBracky

Answer 1

您需要通过名称打开工作表，并使用 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 

- Hadi

---

谢谢，select()函数起作用了，尽管我必须按照我的格式使用它，你的那个给了我一个运行时错误。xlSheet = DirectCast(xlBook.Worksheets("Sheet1"), Worksheet) xlSheet.Select() - JamesBracky