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")
Application.DisplayAlerts = True
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
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