Home > other >  SaveAs in Excel Throws an Error on VB.net
SaveAs in Excel Throws an Error on VB.net

Time:12-08

I am trying to make a program that trims the excel file. I want to keep the original file and just save as the new copy to a new destination with a modified name. My code gives me an error and I'm not sure what I'm missing. Also, is there any way to actually just add a range to delete columns/rows instead of doing it one by one?

Exception thrown: 'System.Runtime.InteropServices.COMException' in PO Trimmer.exe An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in PO Trimmer.exe The file could not be accessed. Try one of the following:

  • Make sure the specified folder exists.

  • Make sure the folder that contains the file is not read-only.

  • Make sure the filename and folder path do not contain any of the following characters: < > ? [ ] : | or *

  • Make sure the filename and folder path do not contain more than 218 characters.

      Dim xlApp As Application
      Dim xlBook As Workbook
      Dim xlSheet As Worksheet
    
      If txtTarget.Text = "" Then
          MsgBox("Please select the file you wish to convert!", vbExclamation   vbOKCancel, "Missing Target File")
          btnBrowseTarget.PerformClick()
      ElseIf txtDestination.Text = "" Then
          MsgBox("Please select the folder to save your file!", vbExclamation   vbOKCancel, "Missing Destination Folder")
          btnBrowseDestination.PerformClick()
      Else
          xlApp = CreateObject("Excel.Application")
          xlBook = xlApp.Workbooks.Open(txtTarget.Text)
          xlSheet = xlBook.Worksheets(1)
    
          If rbtnSeca.Checked = True Then
              Dim rg As Excel.Range
    
              rg = xlSheet.Columns("N")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("M")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("L")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("K")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("J")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("I")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("H")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("F")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("E")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("D")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("B")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Columns("A")
              rg.Select()
              rg.Delete()
    
              rg = xlSheet.Rows(1)
              rg.Select()
              rg.Delete()
    
              Dim convertSuccess As Integer = MsgBox("Trimming success.", vbInformation   vbOKOnly, "Excel Trim")
    
              xlBook.SaveAs(txtDestination.Text & "PO_" & Today & "_" & TimeOfDay, XlFileFormat.xlOpenXMLWorkbook)
              xlApp.Quit()
    
    
          ElseIf rbtnMS.Checked = True Then
    
          End If
      End If
    

CodePudding user response:

The compiler doesn't know what Workbook is with Option Strict on (it should always be on). I was able to qualify with Excel because of my Imports statement.

I changed the message box format. The values need the bitwise Or to combine the correct flags.

It is not a good idea to call events. This can have other implications. Move the code to a separate method and call that from this code and button click code.

Don't create you Excel objects until your conditions are met. I was able to shorten the code a bit by combining contiguous ranges.

Quit is not going to completely clean up the interop objects. There are loads of pages on the internet dealing with this problem.

Imports Excel = Microsoft.Office.Interop.Excel

Private Sub btnBrowseDestination_Click(sender As Object, e As EventArgs) Handles btnBrowseDestination.Click
    BrowseDestination()
End Sub

Private Sub btnBrowseTarget_Click(sender As Object, e As EventArgas) Handles btnBrowseTarget.Click
    BrowseTarget()
End Sub

Private Sub BrowseTarget()
    'Code moved from btnBrowseTarget.Click
End Sub

Private Sub BrowseDestination()
    'Code moved from btnBrowseDestination.Click
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If txtTarget.Text = "" Then
        MsgBox("Please select the file you wish to convert!", MsgBoxStyle.Exclamation Or MsgBoxStyle.OkCancel, "Missing Target File")
        BrowseTarget()
    ElseIf txtDestination.Text = "" Then
        MsgBox("Please select the folder to save your file!", MsgBoxStyle.Exclamation Or MsgBoxStyle.OkCancel, "Missing Destination Folder")
        BrowseDestination()
    Else
        If rbtnSeca.Checked = True Then
            Dim xlApp As New Excel.Application()
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
            xlBook = xlApp.Workbooks.Open(txtTarget.Text)
            xlSheet = DirectCast(xlBook.Worksheets(1), Excel.Worksheet)
            Dim rg = DirectCast(xlSheet.Columns("H:N"), Excel.Range)
            rg.Select()
            rg.Delete()
            rg = DirectCast(xlSheet.Columns("D:F"), Excel.Range)
            rg.Select()
            rg.Delete()
            rg = DirectCast(xlSheet.Columns("A:B"), Excel.Range)
            rg.Select()
            rg.Delete()
            rg = DirectCast(xlSheet.Rows(1), Excel.Range)
            rg.Select()
            rg.Delete()
            Dim convertSuccess As Integer = MsgBox("Trimming success.", MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "Excel Trim")
            xlBook.SaveAs(txtDestination.Text & "PO_" & Today & "_" & TimeOfDay, Excel.XlFileFormat.xlOpenXMLWorkbook)
            xlApp.Quit()
        End If
    End If
End Sub
  • Related