vb.net Visual Studio 2015 - 操作 Excel？

Visual Studio 2015 - Manipulating Excel?

提问by Tom Chambers

I have 750 Excel files that I want to

我有 750 个我想要的 Excel 文件

1. clean by deleting columns of data that have a heading with an asterisk,
2. then take some of that data and put it in a new workbook worksheet, and other data into the same workbook worksheet, and some other data into a second new workbook.

1. 通过删除标题带有星号的数据列进行清理，
2. 然后将其中一些数据放入新的工作簿工作表中，将其他数据放入同一个工作簿工作表中，将其他一些数据放入第二个新工作簿中。

I created a WPF project in Visual Studio 2015 with a little dialog box with 2 radio buttons for

我在 Visual Studio 2015 中创建了一个 WPF 项目，其中有一个带有 2 个单选按钮的小对话框

1. clean data,
2. produce new files.

1. 干净的数据，
2. 产生新文件。

This is my VB code:

这是我的VB代码：

    Class MainWindow
    Dim wb As Microsoft.Office.Interop.Excel._Workbook
    Dim ws As Microsoft.Office.Interop.Excel._Worksheet
    Dim iCol As Integer
    Dim strName As String
    Dim iIndex As Integer
    Dim strPath As String
    Dim strFile As String

    Private Sub button_Click(sender As Object, e As RoutedEventArgs) Handles button.Click
        If cleanRadioButton.IsChecked = True Then
            strPath = "c:\test\old\"
            strFile = Dir(strPath & "*.csv")
            Do While strFile <> ""

                wb = wb.Open(Filename:=strPath & strFile)

                'Loop through the sheets.
                For iIndex = 1 To Application.Worksheets.Count
                    ws = Application.Worksheets(iIndex)

                    'Loop through the columns.
                    For iCol = 1 To ws.UsedRange.Columns.Count
                        'Check row 1 of this column for the char of *
                        If InStr(ws.Cells(10, iCol).Value, "*") > 0 Then
                            'We have found a column with the char of *
                            ws.Columns(iCol).EntireColumn.Delete
                            ws.Columns(iCol + 1).EntireColumn.Delete
                            ws.Columns(iCol + 2).EntireColumn.Delete
                        End If
                    Next iCol

                Next iIndex
                wb.SaveAs(Filename:="C:\test\new\" & wb.Name, FileFormat:=xlOpenXMLWorkbook)
                wb.Close(SaveChanges:=False)
                strFile = Dir()
            Loop
            MessageBox.Show("The csv files have now been cleaned.  Congrats.")
        Else inputRadioButton.IsChecked = True
            MessageBox.Show("The data has now been split into Trajectory and ForcePlate input files.  High 5.")
        End If
    End Sub
End Class

I get 3 errors but can't work out how to solve them.

我收到 3 个错误，但不知道如何解决它们。

a) Worksheets is not a member of Application [line 19]

a) Worksheets 不是 Application [line 19] 的成员

b) Worksheets is not a member of Application [line 20]

b) Worksheets 不是 Application [line 20] 的成员

c) 'xlOpenXMLWorkbook' is not declared. It may be inaccessible due to its protection level.

c) 未声明“xlOpenXMLWorkbook”。由于其保护级别，它可能无法访问。

采纳答案by R3uK

For a) and b), the pattern is :

对于 a) 和 b)，模式是：

Application.Workbooks.Worksheets

应用程序.工作簿.工作表

For c), easiest way out :

对于 c)，最简单的方法：

Go into VBE from Excel (Alt + F11)

Press F2 to display the Object Browser

Look for xlOpenXMLWorkbook

从 Excel 进入 VBE (Alt + F11)

按 F2 显示对象浏览器

寻找 xlOpenXMLWorkbook

Result : Const xlOpenXMLWorkbook = 51 (&H33)So, just replace it by the value 51!

结果：Const xlOpenXMLWorkbook = 51 (&H33)因此，只需将其替换为值 51！

---

---

Here is your amended code :

这是您修改后的代码：

  Class MainWindow
    Dim wb As Microsoft.Office.Interop.Excel._Workbook
    Dim ws As Microsoft.Office.Interop.Excel._Worksheet
    Dim iCol As Integer
    Dim strName As String
    Dim iIndex As Integer
    Dim wbIndex As Integer
    Dim strPath As String
    Dim strFile As String

    Private Sub button_Click(sender As Object, e As RoutedEventArgs) Handles button.Click
        If cleanRadioButton.IsChecked = True Then
            strPath = "c:\test\old\"
            strFile = Dir(strPath & "*.csv")
            Do While strFile <> ""

                wb = wb.Open(Filename:=strPath & strFile)

                'Loop through the sheets.
                For wbIndex = 1 To Application.Workbooks.Count
                    For iIndex = 1 To Application.Workbooks(wbIndex).Worksheets.Count
                        Ws = Application.Workbooks(wbIndex).Worksheets(iIndex)

                        'Loop through the columns.
                        For iCol = 1 To Ws.UsedRange.Columns.Count
                            'Check row 1 of this column for the char of *
                            If InStr(Ws.Cells(10, iCol).Value, "*") > 0 Then
                                'We have found a column with the char of *
                                Ws.Columns(iCol).EntireColumn.Delete
                                Ws.Columns(iCol + 1).EntireColumn.Delete
                                Ws.Columns(iCol + 2).EntireColumn.Delete
                            End If
                        Next iCol

                    Next iIndex
                Next wbIndex
                'Const xlOpenXMLWorkbook = 51 (&H33)
                wb.SaveAs(Filename:="C:\test\new\" & wb.Name, FileFormat:=51)
                wb.Close(SaveChanges:=False)
                strFile = Dir()
            Loop
            MessageBox.Show ("The csv files have now been cleaned.  Congrats.")
        Else: inputRadioButton.IsChecked = True
            MessageBox.Show ("The data has now been split into Trajectory and ForcePlate input files.  High 5.")
        End If
    End Sub
End Class

回答by dee

To reference a worksheet yau can use either ws = wb.Worksheets(1)or ws = wb.Worksheets("Sheet1")or ws = excelApp.ActiveWorkbook.Worksheets(1)and to use xlOpenXMLWorkbookuse the name of the corresponding Enum XlFileFormatas well: XlFileFormat.xlOpenXMLWorkbook.

要引用工作表，您可以使用ws = wb.Worksheets(1)或ws = wb.Worksheets("Sheet1")或ws = excelApp.ActiveWorkbook.Worksheets(1)并使用xlOpenXMLWorkbook相应枚举的名称XlFileFormat：XlFileFormat.xlOpenXMLWorkbook。

This simplified example opens the workbook Test.xlsx, writes text in cell A1and saves it to new folder.

此简化示例打开工作簿Test.xlsx，在单元格A1 中写入文本并将其保存到新文件夹。

Imports System.IO
Imports Microsoft.Office.Interop.Excel

Public Class MainWindow

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim excelApp As Application
        Dim wb As _Workbook
        Dim ws As _Worksheet
        Dim rng As Range
        Dim strPathOld = "c:\temp\old"
        Dim strPathNew = "c:\temp\new"

        ' get excel application reference
        excelApp = New Application
        excelApp.Visible = True
        excelApp.ScreenUpdating = True

        ' open the workbook
        wb = excelApp.Workbooks.Open(Path.Combine(strPathOld, "Test.xlsx"))

        ' set reference to the sheet with index 1
        ws = wb.Worksheets(1)

        ' or use sheet name
        ' ws = wb.Worksheets("Sheet1")

        ' or use ActiveWorkbook if it exists
        ' ws = excelApp.ActiveWorkbook.Worksheets(1)

        ' write text in cell A1
        rng = ws.Range("A1")
        rng.Formula = "Test123"

        ' save the workbook in new location
        wb.SaveAs(Filename:=Path.Combine(strPathNew, wb.Name), _
              FileFormat:=XlFileFormat.xlOpenXMLWorkbook)

        excelApp.Quit()

    End Sub
End Class

Note: add reference to MS Office Interop for your version of Excel(here example for Excel 2007).

注意：为您的 Excel 版本添加对 MS Office Interop 的引用（此处为 Excel 2007 示例）。