EDN Admin
Well-known member
I have a program written in VB that will pull the data from an Excel workbook. The data can be modified by the user. The user selects the file using a "Browse" button. I want to check when the file is selected that it is not already open by checking
all instances of Excel. Here is the code I am using to do the check:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Sub FileLocation_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FileLocation.TextChanged
Dim EXCELAPP As Excel.Application = Marshal.GetActiveObject("<wbr>Excel.application")
For i As Integer = 1 To EXCELAPP.Workbooks.Count
If EXCELAPP.Workbooks(i).Name = FileName Then
MessageBox.Show("The workbook " & FileName & " must be closed first.")
Dim CloseBook = Interaction.GetObject(<wbr>FilePath)
CloseBook.close(False)
End If
Next
EXCELAPP.Visible = False
FileName is the name of the file i.e. Name.xlsx, whereas FilePath is the full file name i.e. C:/Name.xlsx. Both are public variables shared between multiple forms. I have run into two issues with this code. One, it errors if Excel is not open. And two, it
does not check multiple instances of Excel; its random which instance it will select. I have tried using the System Process commands to run through all processes but that seems to just get me locked in an infinite loop.
Does anyone have any suggestions? I feel like there should be a simple solution like the isFileOpen command in VBA, but I cant seem to figure it out.<br/>
View the full article
all instances of Excel. Here is the code I am using to do the check:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Sub FileLocation_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FileLocation.TextChanged
Dim EXCELAPP As Excel.Application = Marshal.GetActiveObject("<wbr>Excel.application")
For i As Integer = 1 To EXCELAPP.Workbooks.Count
If EXCELAPP.Workbooks(i).Name = FileName Then
MessageBox.Show("The workbook " & FileName & " must be closed first.")
Dim CloseBook = Interaction.GetObject(<wbr>FilePath)
CloseBook.close(False)
End If
Next
EXCELAPP.Visible = False
FileName is the name of the file i.e. Name.xlsx, whereas FilePath is the full file name i.e. C:/Name.xlsx. Both are public variables shared between multiple forms. I have run into two issues with this code. One, it errors if Excel is not open. And two, it
does not check multiple instances of Excel; its random which instance it will select. I have tried using the System Process commands to run through all processes but that seems to just get me locked in an infinite loop.
Does anyone have any suggestions? I feel like there should be a simple solution like the isFileOpen command in VBA, but I cant seem to figure it out.<br/>
View the full article