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

I have a script task which opens an excel file and saves it as a .csv but it saves the sheet that opens with the file whereas I want to choose which sheet to save as a .csv, this is the script I'm currently using:

我有一个脚本任务打开一个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

I think I have to use xlbook.Worksheets("Sheet1").Activate but I can't seem to find it.

我想我必须使用xlbook.Worksheets(“Sheet1”)。激活但我似乎无法找到它。

1 个解决方案

#1

You have to open worksheet by it's name and Select it using Select() function:

您必须按名称打开工作表并使用Select()函数选择它:

xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Select()

Your code will be like the following:

您的代码将如下所示:

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

#1