Member Avatar for deep_3657

Hello,

I want to know is it possible to access and modify content of a Excel worksheet dynamically inserted as OLE object in a form. I wish to check and update values of various cells by cell number of the worksheet, but not being sure if possible.

thanks in advance

  • 3 Contributors
  • 7 Replies
  • 210 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by AV Manoharan

Dani AI

Generated

Two practical options will solve this. Either (A) talk to the embedded OLE object exposed by your form/container and drive Excel’s object model in-place, or (B) open and automate the workbook file with Excel (CreateObject / Interop) and read/write cells by address. If you only need to change a few fixed cells in a template, both work; if you need the changes saved back to a file or to multiple users, automating the workbook on disk is safer because embedded OLE objects are often a stored copy and not a transparent link to the original file. (learn.microsoft.com)

In-place (embedded) example — Access/VB6 style (late binding). Activate the control if needed, get its automation object, then use Range or Cells to read/write:

' Access form / VB6 (OLE control named OLE1)
' Activate (Access example)
Me!OLE1.Action = acOLEActivate

' Get the worksheet object from the embedded Excel
Dim ws As Object
Set ws = Me!OLE1.Object.Worksheets(1)

' Read / write by cell address
Dim v As Variant
v = ws.Range("B5").Value
ws.Range("B5").Value = "NewValue"

Using the control’s Object property exposes Excel’s Worksheet/Workbook members directly. (learn.microsoft.com)

Automating the workbook file (recommended when you must persist changes). VB6 (late binding) and VB.NET (Interop) examples:

' VB6 late binding
Dim xl As Object: Set xl = CreateObject("Excel.Application")
Dim wb As Object: Set wb = xl.Workbooks.Open("C:\temp\invoice_template.xlsx")
Dim ws As Object: Set ws = wb.Worksheets(1)
ws.Cells(5,2).Value = "Invoice123"
wb.Save: wb.Close
xl.Quit
' VB.NET (Interop)
Dim app As New Microsoft.Office.Interop.Excel.Application()
Dim wb = app.Workbooks.Open("C:\temp\invoice_template.xlsx")
Dim ws = CType(wb.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
ws.Range("B5").Value = "Invoice123"
wb.Save(): wb.Close(): app.Quit()
' release COM objects (Marshal.ReleaseComObject) in production

See Microsoft examples for Excel automation and Range.Value usage. (learn.microsoft.com)

Troubleshooting & best practices: prefer named ranges (templates change less often than hard-coded A1 addresses); call the container’s activate verb before .Object if you get errors; use late binding if you don’t want compile-time references; always release COM objects (or Marshal.ReleaseComObject) to avoid orphan Excel.exe processes; do not use Office automation from unattended server processes (it’s unsupported and unstable) — instead use Open XML/third‑party libraries for server scenarios. (learn.microsoft.com)

References above show the Object property and Excel automation patterns you can apply directly to the examples in this thread (notes from and point you to these two approaches).

Recommended Answers

All 7 Replies

Member Avatar for AV Manoharan

Why don't you try it?

There is nothing impossible when you get the connection and permission.

Member Avatar for deep_3657

Why don't you try it?

There is nothing impossible when you get the connection and permission.

Yes I want to use it, but have no idea about how to go about it

Member Avatar for QVeen72

Hi Deep,

U r opening Excel using "Excel Object " or by Opening in "OLE Control"...?

I have the code if u r opening with
Excel Object.

Regards
Veena

Member Avatar for deep_3657

Hi Deep,

U r opening Excel using "Excel Object " or by Opening in "OLE Control"...?

I have the code if u r opening with
Excel Object.

Regards
Veena

I have embedded excel as a OLE object

Member Avatar for AV Manoharan

I have embedded excel as a OLE object

See, I requested you to try.

See when you have the connection as an OLE object its behaviour is just like a table of the database. Each sheet corresponding to one table and within each sheet the columns represent table fields.

the record set object works fine here also.

Try and let me know your progress.

Member Avatar for deep_3657

But the excel documents I am working with are not the set of records like we have in database.

I am working with excel files which are soft copies of various physical documents like invoice, quotations etc. Programitically I would say various informations are scattered all around.

However Cell Number where a specific information is found is always fixed. This is the reason i wish to access is by cell numbers rather than by database approach.

Member Avatar for AV Manoharan

But the excel documents I am working with are not the set of records like we have in database.

I am working with excel files which are soft copies of various physical documents like invoice, quotations etc. Programitically I would say various informations are scattered all around.

However Cell Number where a specific information is found is always fixed. This is the reason i wish to access is by cell numbers rather than by database approach.

Then I suggest, the recordset is to be populated by two indexes to draw row as well as coulums.

That is, suppose your recordset is in the second record, then the raw corresponds to the cursor location second among the records.

Then you have to access columns right with the recordsetname.Fields(index)-which is your columns in excel in that row

recordsetname.MoveNext increases the row count to one
and again your column index has to reinitialise else it will be out of your excel columns.

Now, you got it I hope.
Get back to the thread

regards
AV Manoharan

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.