Member Avatar for BroncoTrojan

I cannot figure out the declarations to write to an excel file that is already open.

I open the excel file and make it visible with this:
Private Sub Button1_Click
Dim objExcel As New Excel.Application
Dim objWB As Excel.Workbook
objWB = objExcel.Workbooks.Open("F:\My Software\files\test.xls")
objExcel.Visible=true
End Sub

What are the declarations needed to write to this open this file and write to it?
Private Sub Button2_Click
[[I DON'T KNOW WHAT TO PUT HERE]]
row = 1
column = 1
objExcel.Cells(row, column).Value = "cell data"
End Sub

Thank you,
BroncoTrojan

  • 3 Contributors
  • 4 Replies
  • 278 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by BroncoTrojan

Dani AI

Generated

A short, practical summary and a few safe practices that tie the thread together.

The symptom in this thread came from opening the workbook in one event and trying to write to it from another while the Excel COM objects lived only in the local handler. Keeping the Excel Application and Workbook references at form/class scope prevents the runtime from dropping the COM wrappers between events — this is what ended up doing. The approaches suggested by (working with the active sheet) and (using Worksheet/Range objects) are both valid; prefer an explicit Worksheet reference when multiple workbooks or Excel instances might be present so writes go to the intended file.

Attaching reliably to an already-open Excel process can be done with the running-object mechanism; fall back to creating a new instance only if none is available. Example pattern:

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Try
  xlApp = CType(System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
Catch ex As System.Runtime.InteropServices.COMException
  xlApp = New Microsoft.Office.Interop.Excel.Application()
End Try

' use xlApp.Workbooks(...) or an explicit Worksheet object for cell updates

Always save/close and fully release COM references (Range, Worksheet, Workbook, Application) to avoid orphaned excel.exe processes. Wrap operations in Try/Finally and release objects in reverse creation order, then force garbage collection:

If wb IsNot Nothing Then
  wb.Save()
  wb.Close(False)
  System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
  wb = Nothing
End If

If xlApp IsNot Nothing Then
  xlApp.Quit()
  System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
  xlApp = Nothing
End If

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

Notes: Office automation is not supported for server-side/headless scenarios — use Open XML or libraries such as EPPlus/ClosedXML for file-only manipulation. Also ensure automation runs from an STA thread in desktop apps. Combining class-scope references, explicit Worksheet usage, and the cleanup pattern above gives predictable writes to an open workbook and avoids leftover Excel processes.

Recommended Answers

All 4 Replies

Member Avatar for lolafuertes

it is enough to change

objExcel.ActiveSheet.Cells(row, column).Value = "cell data"
Edited by lolafuertes because: n/a
Member Avatar for bill51

Alternatives to first reply - working from memory but tooltips will provide correct spelling and parameters! And other objects
Dim sht as Excel.Worksheet
Dim rng as Excel.Range
'sheet does not need to be active or visible
sht = objWorkbook.Worksheets("Sheet name")
rng = sht.Range("A1")
rng.value = " some value"
rng.Offset(1,10).value="something else"
'get to end of sheet content
rng=rng.Specialcells(xlEnd).Offset(1,1)

Member Avatar for BroncoTrojan

Thank you for your help - it is working now. I used both the objExcel.ActiveSheet.Cells(row, column).Value = "cell data" and
sht = objWorkbook.Worksheets("Sheet name")
in my solution. I also changed my declarations in the Public Class for the form to
Private objExcel As New Excel.Application
Private objWB As Excel.Workbook

You saved me a lot of time and frustration - I am most grateful!
BroncoTrojan

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.