VB WinForm实现Excel转TXT遇Workbooks未识别问题求助

Hey there! I see you're new to Visual Basic and hitting a snag with Excel interop in your Windows Form project. Let's break down what's going wrong and fix it step by step.

Why You're Seeing This Error #

The Workbooks object isn't being recognized for two key reasons:

1. Missing COM Reference: You haven't added the required Microsoft Excel Object Library to your project, so the compiler doesn't know about the Excel interop types.
2. No Excel Application Instance: Workbooks is a property of an Excel.Application object—you can't call it directly without first creating an instance of the Excel application.

Step 1: Add the Excel COM Reference #

First, let's add the necessary reference to your project:

• Right-click your project in the Solution Explorer → Select Add → Reference.
• Go to the COM tab → Scroll down and find Microsoft Excel xx.x Object Library (xx.x depends on your Excel version, e.g., 16.0 for Excel 2019/365).
• Check the box next to it and click OK.

Step 2: Fix Your Code #

Now let's update your code to properly use the Excel interop, including handling the application instance, opening the workbook, saving it as a .txt file, and cleaning up resources (critical to avoid leftover Excel processes running in the background!).

Here's the corrected code:

Imports Microsoft.Office.Interop.Excel
Imports System.Windows.Forms

Public Class Form1
    Dim path As String = "C:\Users\Dustin\Desktop\"
    Dim filename1 As String

    Private Sub txtBoxExcelFileNameString_TextChanged(sender As Object, e As EventArgs) Handles txtBoxExcelFileNameString.TextChanged
        filename1 = txtBoxExcelFileNameString.Text
    End Sub

    Private Sub btnExcelSaveAs_Click(sender As Object, e As EventArgs) Handles btnExcelSaveAs.Click
        ' Declare Excel objects
        Dim excelApp As Application = Nothing
        Dim workbook As Workbook = Nothing

        Try
            ' Create a new Excel application instance
            excelApp = New Application()
            ' Open the target workbook
            workbook = excelApp.Workbooks.Open(Filename:=path & filename1)

            ' Define the save path for the .txt file
            Dim savePath As String = path & System.IO.Path.GetFileNameWithoutExtension(filename1) & ".txt"
            ' Save workbook as Windows-style text file
            workbook.SaveAs(Filename:=savePath, FileFormat:=XlFileFormat.xlTextWindows)

            MessageBox.Show("File saved successfully as .txt!")
        Catch ex As Exception
            MessageBox.Show($"An error occurred: {ex.Message}")
        Finally
            ' Clean up resources to prevent Excel from running in the background
            If workbook IsNot Nothing Then
                workbook.Close(SaveChanges:=False)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            End If
            If excelApp IsNot Nothing Then
                excelApp.Quit()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
            End If
        End Try
    End Sub
End Class

Key Changes Explained: #

• Correct Import: Imports Microsoft.Office.Interop.Excel gives you access to all Excel interop types (your original import was too broad).
• Excel Application Instance: We create an Application object (excelApp) and use excelApp.Workbooks.Open() instead of calling Workbooks directly.
• Save As Text: The SaveAs method uses XlFileFormat.xlTextWindows to generate a Windows-compatible text file.
• Resource Cleanup: The Finally block ensures we close the workbook, quit Excel, and release COM objects to avoid memory leaks and orphaned processes.

Your Original Code & Screenshot #

For reference, here's the code you shared:

Imports Microsoft.Office.Interop
Imports Microsoft.VisualBasic
Public Class Form1
    Dim path As String = "C:\Users\Dustin\Desktop\"
    Dim filename1 As String
    Private Sub txtBoxExcelFileNameString_TextChanged(sender As Object, e As EventArgs) Handles txtBoxExcelFileNameString.TextChanged
        filename1 = txtBoxExcelFileNameString.Text
    End Sub
    Private Sub btnExcelSaveAs_Click(sender As Object, e As EventArgs) Handles btnExcelSaveAs.Click
        Workbooks.Open Filename:=path & filename1
    End Sub
End Class

And your error screenshot:

Give these steps a try, and let me know if you run into any other hiccups!

内容的提问来源于stack exchange，提问作者Busta