如何在不提示用户的情况下覆盖 Excel 应用程序

Question

任何人都可以帮助我如何在不提示 VB.Net 中的用户的情况下覆盖 excel 文件..

我已经尝试过这段代码，但它不起作用..

Dim xlsApp As New Excel.Application
Dim xlsBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim dir As String = Application.StartupPath & "\Template\SampleTemplate.xls"
xlsBook = GetObject(dir)
xlsSheet = xlsBook.Sheets("Per BPA Error Report")


xlsSheet.Range("C2:T2").Merge()

xlsApp.DisplayAlerts = False
xlsSheet.SaveAs(Application.StartupPath & "\Template\SampleTemplate.xls")
xlsBook = Nothing
xlsSheet = Nothing
xlsApp.Quit()

Answer 1

Public Sub WriteExcelFile(ByVal ExcelFilePath As String) 
    Dim excel As Application = New Application
    Dim w As Workbook = excel.Workbooks.Open(ExcelFilePath)
    Dim sheet As Worksheet = w.Sheets(1)
    sheet.Cells(x + 1, 1) = 10
    x = x + 1
    excel.DisplayAlerts = False
    w.Save()
    w.Close()
End Sub

Answer 2

如果您只想覆盖当前存在的文件，那么先删除它然后保存新文件可能会更容易。因此只需使用 System.IO.File.Delete 即可。

Answer 3

为什么需要使用另存为？
查看代码，您正在尝试写入同一个文件。请改用保存。

Answer 4

 Private Sub f_ExcelWorksheet()
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object

    'Start a new workbook in Excel
    oExcel = CreateObject("Excel.Application")

    'oBook = oExcel.Workbooks.Add 'This is when we want to create new excel sheet

    'If we want to open exisiting excel sheet
    oBook = oExcel.Workbooks.Open("C:\Users\adimadud\Desktop\Test.xlsx")

    'Add data to cells of the first worksheet in the new workbook
    oSheet = oBook.Worksheets(1)

    'This will find the lastRow in the sheet
    Dim lastRow As Integer = oSheet.UsedRange.Rows.Count

    'This is next emptyRow in the sheet
    Dim emptyRow As Integer = lastRow + 1
    'oSheet.Range("A1").Value = "Last Name"
    'oSheet.Range("B1").Value = "First Name"
    'oSheet.Range("A1:B1").Font.Bold = True
    'oSheet.Range("A2").Value = "Doe"
    'oSheet.Range("B2").Value = "John"



    MessageBox.Show(lastRow)
    oSheet.Cells(emptyRow, 1).value = "Test"
    oSheet.Cells(emptyRow, 2).value = "Test"
    'Now again find the lastRow in the excel sheet
    lastRow = oSheet.UsedRange.Rows.Count
    'This is next emptyRow in the sheet
    emptyRow = lastRow + 1

    'This will not prompt the user to overwrite the excel sheet
    oExcel.DisplayAlerts = False
    oBook.Save()
    oBook.Close()

    'Save the Workbook and Quit Excel
    'This will prompt the user to overwrite the excel sheet
    'oBook.saveas("C:\Users\adimadud\Desktop\Test.xlsx")

    oExcel.Quit()

End Sub