Visual Basic - 保存和编辑 Excel 表单

Question

我遇到了一个错误。我正在尝试创建一个创建，加载和保存excel文件的程序。我只使用Visual Basic，所以请不要用其他语言回答。

该程序旨在创建一个名为"示例"的 excel 文件或加载它(如果它已经存在(。

        If (My.Computer.FileSystem.FileExists("D:ProgrammingExample.xlsx")) Then
        xlWorkBook.Save()
    Else
        xlWorkBook.SaveAs(Filename:="D:ProgrammingExample.xlsx",
        FileFormat:=51)
    End If

出于某种原因，它似乎没有这样做，而是创建了一个名为"book1.xlsx"而不是"example.xlsx"的文件。

此外，当它保存时，它似乎使 excel 在后台保持打开状态。这是我的完整代码：

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    End Sub
    Private Sub Create_Click(sender As Object, e As EventArgs) Handles Create.Click
        xlWorkBook = xlApp.Workbooks.Add
        xlApp.Visible = True
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        With xlWorkSheet
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"
            .Range("B1").Value = "Loan Repayment"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1200.00"
            .Range("B4").Value = "1300.00"
            .Range("B5").Value = "1600.00"
            .Range("A6").Value = "Total Paid"
            .Range("B6").Formula = "=Sum(B2:B5)"
        End With
        Test.Text = xlWorkSheet.Range("A1").Value
    End Sub
    Private Sub Save_Click(sender As Object, e As EventArgs) Handles Save.Click
        If (My.Computer.FileSystem.FileExists("D:ProgrammingExample.xlsx")) Then
            xlWorkBook.Save()
        Else
            xlWorkBook.SaveAs(Filename:="D:ProgrammingExample.xlsx",
            FileFormat:=51)
        End If
        xlWorkBook.Close()
        xlApp.Quit()
        ReleaseAll(xlApp)
        ReleaseAll(xlWorkBook)
    End Sub
    Private Sub ReleaseAll(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

干杯感谢任何帮助。 :D

Answer 1

问题之所以出现，是因为我要创建同一工作簿的多个实例，而不是在文件已经存在的情况下打开它。

这将修复代码：

        If (My.Computer.FileSystem.FileExists(Application.StartupPath + "Book1.xlsx")) Then
        xlWorkBook = xlApp.Workbook.Open(Application.StartupPath + "Book1.xlsx")
    Else
        xlWorkBook = xlApp.Workbooks.Add
    End If

然而，这会产生另一个问题。错误说：

System.MissingMemberException: 'Public member 'Workbook' on type 'ApplicationClass' not found.'

以打开工作簿。(第15页(。