如何在不提示用户的情况下覆盖 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()

- Mark

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

- Khalid

Answer 2

如果您只想覆盖当前文件，最好先将其删除，然后保存新文件。因此，只需使用 System.IO.File.Delete。

- Hans Olsson

Answer 3

 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

- TanuAD

Answer 4

为什么需要使用SaveAs？看一下代码，你试图写入同一个文件。应该使用Save。

- shahkalpesh