Usinging VBScript to get Data from Excel

[Litalu]

I have searched through the forums but have not been able to find a simple example of how to get a single excel cell into a 3rd party application using VBScript. I have an open excel sheet which has information in a cell that will change periodically. The excel sheet is always open so I need to be able to use VBScript to look at the work sheet and get the value of this cell. As simple as possible would be excellent in order to get me started.

Thanks.

 

[PHV]

Take a look at GetObject

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Litalu]

I can see where Getobject might be used but I can't seem to find enough information to properly format the code. I have the path but what do I use for the objecttype assuming that the appname is excel.exe

Thanks,

 

[Litalu]

I can see where Getobject might be used but I can't seem to find enough information to properly format the code. I have the path but what do I use for the objecttype assuming that the appname is excel.exe

Thanks,

 

[PHV]

Something like this:
Set myWB = GetObject("\path\to\workbook.xls")
myVar = myWB.Application.Cells(1,1) 'A1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Litalu]

Here is my script so far:

Set appexcel = GetObject(, Excel.Application)
Set wks = appexcel.Sheets(1)
Var1 = wks.Cells(1, 1)

It gives me a ActivX component can't create Object error whenever I tried to execute it. I have the excel workbook open but I can't seem to get it to work.

Any further help would be very much appreciated

 

[PHV]

And this ?
Set appexcel = GetObject(, "Excel.Application")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Litalu]

According to the information that I have been able to find, the GetObject(, "Excel.Application") is used when the application that you are reference (Excel in this case) is currently running on the machine. The example that you provided earlier would be used in the case where Excel did not currently have the file that was being referenced open.

 

[Litalu]

Here is the reference that I was talking about:


"If you omit the pathname argument but leave the comma

Set wordApp = GetObject(, "Word.Application")

VBScript returns an existing instance of the Application object if one exists."

 

[PHV]

I suggested you replace this:
Set appexcel = GetObject(, Excel.Application)
By this:
Set appexcel = GetObject(, "Excel.Application")

Have you tried that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Litalu]

Thanks for your help but I am still having some issues.

Currently the following script works within the excel VisualBasic editor but does not work when I move it into my application that has the VBScript engine in it.

Set appexcel = GetObject(, "Excel.Application")
Set wsobj = appexcel.Worksheets("Sheet1")
Var1 = wsobj.Cells(1, 1)

When I attempt to run it from the script engine, I again get the "The following error occurred: 'ActiveX component can't create object: 'GetObject''."

Not sure what I am doing wrong.