Help! Office 2007 and VBS

[Keyster86]

OK, I had Office 2003 and created a script that works with Excel 2003. Then I recently updated to 2007. Now the script fails. I am trying to run the below code without getting the below error message. Please help. Any ideas/solutions?

Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = TRUE
Set myWorkbook = myExcel.Workbooks.Open("C:\test.xlsx")

~Error~
Script: C:\test.xlsx
Line: 3
Char: 1
Error: The server threw an exception.
Code: 80010105
Source: (null)
~Error~

I suspect the object library refference version is the core of the problem, but I do not know and I need a solution. Any ideas?



V/r,

SPC Key
United States Army

 

[PHV]

I'd check the security level.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Keyster86]

Hmm, how would I do that and if I did, what would be the best setting?

V/r,

SPC Key
United States Army

 

[NWBeaver]

I am curious about the file extension of C:\test.xlsx
What does the extra x at the end of the .xlsx do?
Excel spreadsheets usually just have a .xls extension.

 

[NWBeaver]

Never mind... I know .xlsx is a feature of Office 2007.

 

[NWBeaver]

I do know that Office 2007 uses a reference to the Microsoft Office 12.0 Object Library where 2003 used the
Microsoft Office 11.0 Object Library.

 

[markdmac]

The code is fine. As PHV suggested, check security.

In Excel 2007 click the Office Logo Button. Click Excel options. Click Trust center. Click Trust Center Settings.

Set security for Trusted Locations and under Macro Security.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[Keyster86]

Yep, still getting "Server threw an exception" error.

* Tried the script on multiple computers - Same error happens.
* Tried setting the "Trusted Locations" to the desktop, then running the script from the desktop - Same error happens.
* Tried checking the "Allow Trusted Locations on my network (not recommended)" checkbox - Same error happens.
* Tried setting Macro Settings to "Enable all macros (not recommended; potententially dangerous code can run" radio button - Same error happens.

I am still think that I need to refference the Office 2003 Microsoft Office 11.0 Object Library through VBA (In Excel VBA window, go to Tools > Refferences...). However, I am not sure where the refference file for the "Microsoft Office 11.0 Object Library" is located or where it can be downloaded. Any ideas/suggestions?

V/r,

SPC Key
United States Army

 

[NWBeaver]

I am running Office 2003, and from what I can read when I look at the path for the Office 11.0 Object library, the location is:
C:\Program Files\Common Files\Microsoft Shared\OFFICE11

There are a number of .DLL's in the folder, including a MSO.DLL, which I suspect is the one that is being referenced.

 

[tsuji]

[ul][li]Open the registry with regedit.exe[/li]
[li]go to the branch hklm\software\classes\clsid[/li]
[li]search for target string "excel.application"[/li]
[li]readout the keys' default of ProgID and VersionIndependentProgID from the finding(s)[/li]
[li]readout also the key LocalServer's default[/li]
[/ul]
You then get all the necessary information. Of course, if the m/c has no Office/Excel installed, you won't be able to do automation script with excel.

 

[Keyster86]

Interesting note:

If I open Excel prior to running the below short script. No error occurs. (which I guess can be my current work around until a better solution is found).

Function OpenEx

Set myExcel = [COLOR=red]GetObject(, [/color]"Excel.Application")

myExcel.Visible = TRUE
Set myWorkbook = myExcel.Workbooks.Open("C:\test.xlsx")

End Function

However, if Excel is closed and I use the below code...I still get the "Server threw an exception" error. Weird. Any thoughts?

Function OpenEx

Set myExcel = [COLOR=red]CreateObject([/color]"Excel.Application")

myExcel.Visible = TRUE
Set myWorkbook = myExcel.Workbooks.Open("C:\te.xlsx")

End Function

V/r,

SPC Key
United States Army

 

[NWBeaver]

In some cases you have to have admin rights to use the CreateObject method.

I also read about a case where a similar problem was solved by using a time delay after the CreateObject such as:
wscript.sleep 2000
To wait 2 seconds in this example, before continuing with the next line of code, in case there is some sort of timing issue.

 

[Keyster86]

I appericiate everyone's responses; however, problem still persists.

I have tried running the script with admin rights through "run as..." function with IE.
I have tried running the script with logging into system solely as a admin.
I have tried running the script with normal user rights.
- all result in same problem.

I also tried below code with two seconds and then with six second interval wait periods - same problem.

Function OpenEx

Set myExcel = CreateObject("Excel.Application")
wscript.sleep 2000
myExcel.Visible = TRUE
wscript.sleep 2000
Set myWorkbook = myExcel.Workbooks.Open("C:\test.xlsx")
wscript.sleep 2000
End Function

V/r,

SPC Key
United States Army

 

[NWBeaver]

Where are you trying to create the Excel object, on the local machine, or a network server?

What about another application such as anti-virus, could that be blocking the object creation?