OLE/COM/VBS & Excel revisited

av8rmike · October 30, 2002, 2:33pm

When we last saw av8rmike, he was asking in this thread about transferring data between Excel/Access and a C++ app using OLE/COM technology. I talked to another co-worker, who claimed that there was a way, using Excel and its VB capabilities, to actively transfer data to the C++ app while it was running. Obviously this program had OLE capability compiled into it. Unfortunately this co-worker doesn’t know enough about VB to know how to do it, and the 3rd party software he saw being demonstrated was government proprietary.

So, any of the people who made suggestions last time want to chime in on what I’ve described? Is it possible, or are my co-workers misinformed?

Starbury · October 30, 2002, 5:43pm

Are you asking whether you can grab datting from the Access file while the C++ app is running or while Access is running?

IIRC, MS Access puts a single user lock on its database – in other words, only one application can be using a specific .mdb (access database) file at a time. I’m not sure if this is the same for Excel, but I would guess so. Because of this, I don’t think an outside application can access the database file while Access has it open.

There are a couple of ways around this that I can think of:

(1)Create a VB (or C++ or any other Visual Studio language) application that works as a “wrapper” for your Access database. As explained in the previous thread you mentioned, you can use ADO to connect to the Access database, or use a FlexGrid, or create your own controls. That way, you will lock the database while you are updating,deleting,etc. and will release the lock as soon as the task is finished.

(2)For Excel, you have two options: ADO and OLE. You can use OLE Automation in your VB Application, so that you can run an instance of Excel within your vb app (just drop an OLE control on your VB form and set the properties to load the proper excel file or you can do it through code). You can then automate the process of locking and unlocking the excel worksheet, through code that you have written.

(2b)Or you can just use an OLE DB driver to connect to your Excel file and handle it the same way as an access database (I’ve done this before, and its very easy to do if you’re familiar with ADO at all). Excel uses the same OLE DB driver as Access (“OLE DB Provider for Jet”)

Here’s an example of some of the things you can do with Excel and ADO:

'*************************
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset

' Open a connection to the Excel workbook.
Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source=" & App.Path & "\Expenses.xls;" &

_
“Extended Properties=”“Excel 8.0;HDR=Yes;”";"
oConn.Open

' Create a command object and set its ActiveConnection
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn

' This SQL statement selects a cell range in a particular worksheet.

’ oCmd.CommandText = "SELECT * from Expenses$A2:C4"

' This SQL statement selects a named cell range defined in the workbook.
oCmd.CommandText = "SELECT * from `Range1`"

' Open a recordset containing the worksheet data.
Set oRS = New ADODB.Recordset
oRS.CursorLocation = adUseServer
oRS.Open oCmd, , adOpenKeyset, adLockOptimistic

Debug.Print oRS.RecordCount

' Update last row
oRS.MoveLast
oRS(0).Value = -1
oRS.Update

' Add a new row
oRS.AddNew
oRS(0).Value = 7
oRS(1).Value = 8
oRS(2).Value = 9
oRS.Update

Debug.Print oRS.RecordCount

' Clean Up
Set oRS = Nothing
Set oCmd = Nothing
oConn.Close
Set oConn = Nothing

'*********************************

In either case, you would need to have a COM Object that would be able to communicate with your C++ app. After each change to the database (or excel worksheet), you could raise an event within the COM object to pass the new data to your C++ app.

I don’t know if this will help you at all…if you want, I can probably write some sample code to show you what you would need to do.

av8rmike · October 30, 2002, 7:35pm

Well, apparently, in the demonstration my co-workers and supervisor saw, both the C/C++ app and Excel are open at once, and able to pass data between the two in real-time. Do ADO or OLE have this capability or could something else have been used? I’m pretty sure that the demonstration program used a VB wrapper, as you’ve described.

micco · October 30, 2002, 8:49pm

You can use both ADO and OLE to connect applications to Excel. Normally, you’d use OLE automation, which allows you to specify ranges, control formatting etc. You can also use ADO, which treats the Excel file more like a database and allows you to run SQL queries.

Starbury · October 31, 2002, 6:10pm

Ok, I just wrote a small VB app to test this out. I had VB run a set of queries and updates on an existing excel workbook, while I had both VB and the excel workbook open on my screen. When VB made the changes, it automatically updated the window that had the excel workbook open. So, yes, you can do this with Excel. But you definitely can’t do it with Access.

Anyway, its really easy to set it up, just use the code from my last post and modify it to fit your needs. Just change the Data Source property to the location of your excel workbook file (.xls file). Also, if your Excel file doesn’t have any headers, then change HDR=Yes to HDR=No in the previous code.

micco · October 31, 2002, 7:16pm

The only difference with Access is that you won’t see the changes in the Access db take effect dynamically. When you view data in an Access database, you’re actually viewing the results of a query against the underlying data. Even when you open a table view instead of a specific query, you’re looking at an abstraction of the actual data. That means if you have an Access db open and are viewing a table, and another application modifies the data, you won’t see the changes on the screen in real time. You may see change indicators like “#DELETED” appear in fields where the underlying data was deleted, but you won’t see the actual changes until you refresh the query by closing the table view and reopening it. Note that you don’t have to close/reopen Access, just reopen the table view so you have a fresh query result.

Contrary to your earlier statement “I don’t think an outside application can access the database file while Access has it open”, an external application can make an ADO connection to an Access database and run queries against it while a user has the file open in Access. You can hit the db with multiple external applications (it is not locked in single-user mode). I frequently hit MDB files from multiple VB apps and/or ASP connections while the file is open in Access. As noted above, you don’t see the changes in real-time in Access, but any changes that do occur in the real data can be seen by simply refreshing your view of that data.

Starbury · October 31, 2002, 7:48pm

I’ll defer to you on this, micco. I really only use SQL Server and Oracle, so its possible I’ve just been using the wrong settings for my ADO connection to Access dbs, but I’ve never been able to use Access the way you describe.

av8rmike · October 31, 2002, 9:01pm

::furiously bookmarking thread::

Someone in the other thread recommended a book called, “Beginning ATL/COM Programming.” micco, is there a book that discusses in more detail the OLE automation?

What about the C++ program that receives the data from the Excel/OLE automation? How would it exchange information with this VB wrapper?

micco · October 31, 2002, 9:51pm

I don’t do much in C++, but you can access the Excel automation via a COM wrapper in Java, so I don’t think you’d have any problem using it directly from C++. In this case, it’s C++ controlling the link and there’s no need for a VB wrapper. Look at that first link I posted earlier. There are lots of links on that page to more info about doing Excel automation from C++.

VB comes into play if you want to write code in Excel to push data into a C++ app. Then you’re talking about VBA code (not exactly VB, but basically the same) within Excel linking to some exposed interface on the C++ app, probaby COM.