HaPhan HaPhan - 1 year ago 81
Vb.net Question

VB.net manage opened excel file on the fly

In my current project, i need to modify an excel file that is already opened. That mean, i need to see the change in the excel file whenever i modify it.

Since opened file is locked and vb cannot access that file, my solution is to open another excel file, modify its content and then copy to the original file using excel vba, as excel vba can be used to copy data between opening sheets.

Here is the vb code in my other excel file (called TTHT.xlsm):

Private Sub Worksheet_CHANGE(ByVal Target As Range)
Application.ScreenUpdating = False
If Sheet1.[B5].Value = "#" Then
Application.DisplayAlerts = False
With Workbooks.Open(ThisWorkbook.Path & "\PCN.xlsm")
With ThisWorkbook.Sheets("TTHT").[B5]
Sheets("PCN").[B4].PasteSpecial 7
End With
End With
Application.DisplayAlerts = True
End If
End Sub

Runs fine between my 2 opening excel file ( that TTHT one and PCN.xlsm ).

Now all I need to do, is to fill the data into TTHT, and put a "#" into B5 by my code. I think it will work, but it's not..

Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False
oBook = oExcel.Workbooks.Open(TTHTPath)
oSheet = oBook.Worksheets("TTHT")
oSheet.Range("B4").Value = txtValue_Needed.text
oSheet.Range("B5").Value = "#"
oSheet = Nothing
oBook = Nothing
oExcel = Nothing
End Sub

Result is, if i open the PCN file and click button, it won't change. If I close the PCN and click button, it will change..
But i need to see the change on the fly, not by close and reopen each time i need to update.
Can someone shed me some light, as to how, or which function ... either vba or vb.net, should i use ?

Answer Source

If the file is already opened and has focus then you can try looking it up in the running objects table.

Dim ExcelApplication As Excel.Application    
ExcelApplication = GetObject("C:\Folder\ExcelWorkbook.xls")

Then you can just make your modifications on the instance which you referenced while trying to avoid disrupting the user.

The getobject() function is great; here's a link. https://msdn.microsoft.com/en-us/library/e9waz863(v=vs.71).aspx