如何在SSIS 2013中使用Microsoft.Office.Interop.Excel找到VB.Net的正确语法？

Question

我正在使用SSIS将来自Excel 2013的数据加载到SQL Server 2014中。其中一个列的数据超过255个字符。使用源Excel任务只会读取前255个字符并截断其余部分。

我编写了一个.vb脚本来打开文件并将其保存为CSV，同时保留255个字符以后的所有内容，但我还没有找到如何在将其保存为csv文件之前使指定的工作表处于活动状态。

我决定使用Microsoft.Office.Interop.Excel将代码转换为VB.Net以访问文件，但我仍然无法获得正确的语法。我无法找到任何关于使用Microsoft.Office.Interop.Excel的VB.Net的信息。

Public Sub Main()

    Dim FilePath As String = "I:\DSS Clarity\Clarity Technical\METADATA MINING\INPUT_SAP_BO_UNIVERSE_FILES\"
    Dim FileName As String = "UNV Universes.xlsx"


    Dim excel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
    excel.DisplayAlerts = False

    ' Open Excel spreadsheet.
    Dim wb As Workbook
    wb = excel.Workbooks.Open(FilePath & FileName)

    'wb.Worksheets.Select("Tables")    <-- need to make the Tables sheet active - how do i do that?


    wb.SaveAs(FilePath & Left(FileName, InStrRev(FileName, ".")) & "csv", 24)
    wb.Close()

    Dts.TaskResult = ScriptResults.Success
End Sub

我希望看到一张指定工作表的所有数据以CSV格式保存，而不是Excel中一个字段最多只能保存255个字符的限制。

- rmj

Answer 1

我对你的代码进行了以下编辑：

1. 保存之前选择了第一个工作表
2. 编辑了 SaveAs() 函数参数
3. 必须在脚本结束时使用 excel.Quit() 命令关闭应用程序

Public Sub Main()

    Dim FilePath As String = "I:\DSS Clarity\Clarity Technical\METADATA MINING\INPUT_SAP_BO_UNIVERSE_FILES\"
    Dim FileName As String = "UNV Universes.xlsx"


    Dim excel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
    excel.DisplayAlerts = False

    ' Open Excel spreadsheet.
    Dim wb As Workbook
    wb = excel.Workbooks.Open(FilePath & FileName)

    wb.Worksheets(0).Select(Type.Missing) 'Or try wb.Worksheets(0).Select()

    wb.SaveAs(FilePath & Left(FileName, InStrRev(FileName, ".")) & "csv", Excel.XlFileFormat.xlCSV,Excel.XlSaveAsAccessMode.xlNoChange)
    wb.Close(false)
    excel.Quit()

    Dts.TaskResult = ScriptResults.Success

End Sub

- Hadi

---

感谢您的帮助。 “表格”工作表不是工作簿中的第一个工作表。 - rmj

当我运行脚本时，针对新行（两个建议）出现的错误是：“无效索引。（HRESULT 异常：0x8002000B (DISP_E_BADINDEX)）”。 - rmj

wkshtCount = wb.Worksheets.Count 这段代码可以告诉我工作簿中有多少个工作表。在将文件保存为csv之前，我只需要将“Tables”工作表/选项卡设为活动状态即可。 - rmj

谢谢，Hadi。Wb.worksheets("Tables").Select(Type.Missing) 给了我一个错误，然而，通过循环工作表，就像你分享的链接中所示，可以解决问题。 - rmj

@rmj 这很奇怪。对我来说是有效的。我会尝试找出问题所在。 - Hadi