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