Using VB.Net to Open, Update, Close Excel With Other Workbooks Open

June 14, 2021 10:03 am by ManFridayIT

ManFridayIT’s software development services include creating systems in VBA and MS Office automation. For further information, email info@manfridayit.co.uk.

Open, Update and Close Excel Workbook in VB.Net with other Workbooks Open

This VB.Net application checks for any open Excel workbooks, opens an instance of Excel, searches for a specific-named WorkSheet, if the sheet is not found it is created. The first completely empty row is found, the first cell is sequentially numbered and text values inserted into the row cells, the workbook is updated, saved and then the application closes this instance of Excel only, leaving any existing Excel applications open.

Require Normal Imports

Private mExcelProcesses() As Process

Private Sub completeexcel()
ExcelProcessInit("Excel") ‘//sub notes process IDs of any open Excel instances

Dim xlFile As String = "C:\Test\TestBook.xlsx"
Dim i As Integer
Dim record_number As Long = 1
Dim rn As String
Dim lastrow As Long = 0
‘ //Get the Excel application object
Dim excel_app As New Excel.Application()
Dim rng As Excel.Range
‘ //Make Excel invisible
excel_app.Visible = False
Try
‘ Open the workbook
Dim workbook As Excel.Workbook =
excel_app.Workbooks.Open(Filename:=xlFile)

‘ //See if the worksheet already exists
Dim sheet_name As String = "Customers"

Dim sheet As Excel.Worksheet = FindSheet(workbook,
sheet_name)
If (sheet Is Nothing) Then
‘ //Add the worksheet at the end
sheet = DirectCast(workbook.Sheets.Add(
After:=workbook.Sheets(workbook.Sheets.Count),
Count:=1,
Type:=Excel.XlSheetType.xlWorksheet),
Excel.Worksheet)
sheet.Name = sheet_name
End If

Catch ex As Exception
‘//Tidy Excel up if error
MessageBox.Show(ex.ToString + "Process Terminated.")
excel_app.Quit()
ExcelProcessKill("Excel")
Exit Sub
End Try
With sheet
lastrow = .Cells(.Rows.Count, "A").End(Excel.XlDirection.xlUp).row  ‘get row no of last completed record
End With

rng = CType(sheet.Cells(lastrow, 1), Excel.Range)
rn = rng.Value
Try
record_number = CType(rn, Long) ‘turn lastrow into long
record_number = record_number + 1
Catch ex As Exception
‘//Tidy Excel up if error
MessageBox.Show(ex.ToString + "Process Terminated.")
excel_app.Quit()
ExcelProcessKill("Excel")‘//Sub closes this instance of Excel only
Exit Sub
End Try

sheet.Cells(lastrow + 1, 1) = record_number
sheet.Cells(lastrow + 1, 2) = "This Column"
sheet.Cells(lastrow + 1, 3) = "Next Column"
sheet.Cells(lastrow + 1, 4) = "The Column After"

‘// Save the changes and close the workbook
workbook.Close(SaveChanges:=True)

‘Clear up excel exe
excel_app.Quit()
ExcelProcessKill("Excel")‘//Sub closes this instance of Excel only
GC.Collect()
GC.Collect()

End Sub
——————-

‘ //Return true if the workbook has a worksheet with this name
Private Function FindSheet(ByVal workbook As Excel.Workbook,
ByVal sheet_name As String) As Excel.Worksheet
For Each sheet As Excel.Worksheet In workbook.Sheets
If (sheet.Name = sheet_name) Then Return sheet
Next sheet
Return Nothing
End Function
——————-
‘//Get all currently running process Ids for Excel applications
Private Sub ExcelProcessInit(ProcessName As String)
Try
mExcelProcesses = Process.GetProcessesByName(ProcessName) '("Excel")
Catch ex As Exception
End Try
End Sub

‘//Get all currently running process Ids for Excel applications and Kill any not on original array from ExcelProcessInit
Private Sub ExcelProcessKill(ProcessName As String)
Dim oProcesses() As Process
Dim bFound As Boolean

Try

oProcesses = Process.GetProcessesByName(ProcessName)

If oProcesses.Length > 0 Then
For i As Integer = 0 To oProcesses.Length - 1
bFound = False

For j As Integer = 0 To mExcelProcesses.Length - 1
If oProcesses(i).Id = mExcelProcesses(j).Id Then
bFound = True
Exit For
End If
Next

If Not bFound Then
oProcesses(i).Kill()
End If
Next
End If
Catch ex As Exception
End Try
End Sub

---