How to get data from Excel without opening it 1

[makavity]

I am trying to write an application in Extra that would access a shared Excel worksheet and pull information from it, without opening the worksheet. The information would then be displayed in a message box. The worksheet is available to over 1,000 other users, so I'm trying to limit the number of people who would have it open at any time. The intent is to create a quick reference to the information in a spreadsheet. Also, if it is currently open, I would need to avoid the pop-up message in Excel to open as Read Only, or Notify the other user.

I think using the command objExcel.Visible = False still counts as the spreadsheet being open... just not visible, and then you can't close the spreadsheet as it isn't visible to close it.

Some users may already have an Excel spreadsheet open, so there is also the issue of the Personal workbook.

Any thoughts?

Thanks in advance

Jeff

 

[Skie]

Have you tried GetObject? I haven't had to use it, but it looks like it shoiuld work for what you need.

Syntax A GetObject( pathname)
Syntax B GetObject( pathname , class )
Syntax C GetObject( , class )

pathname The path and file name for the object to retrieve.
class A string containing the class of the object.

Comments Use GetObject with the Set statement to assign a variable to the object for use in a Basic procedure. The variable used must first be dimensioned as an Object.

Syntax A of GetObject accesses an OLE2 object stored in a file. For example, the following two lines dimension the variable, FILEOBJECT as an Object and assign the object file "PAYABLES" to it. PAYABLES is located in the subdirectory SPREDSHT:

Dim FileObject As Object
Set FileObject = GetObject("\spredsht\payables")

If the application supports accessing component OLE2 objects within the file, you can append an exclamation point and a component object name to the file name, as follows:

Dim ComponentObject As Object
Set ComponentObject = GetObject("\spredsht\payables!R1C1:R13C9")

Syntax B of GetObject accesses an OLE2 object of a particular class that is stored in a file. Class uses the syntax: "appname.objtype", where appname is the name of the application that provides the object, and objtype is the type or class of the object. For example:

Dim ClassObject As Object
Set ClassObject = GetObject("\spredsht\payables", “turbosht.spreadsheet”)

The third form of GetObject accesses the active OLE2 object of a particular class. For example:

Dim ActiveSheet As Object
SetActiveSheet = GetObject( , “turbosht.spreadsheet”)

 

[calculus]

You may want to try ADO:
faq99-4068

Also, consider opening the Excel file as Read only without the message box. I have a file I saved as read only, so all users open it as read only. Check the Excel forum for details, but dialogs can be avoided like this in Excel:
oxcl.displayalerts = false
Open statement
oxcl.displayalerts = True

Look through the above response also, it looks promising, but the ADO Faq above may prove more useful to you if you're familiar with DB statements.

calculus