0

For a few reasons discussed here I can't open an excel worksheet like this:

Set excel = CreateObject("Excel.Application")
Set workbook = excel.Workbooks.Open(file_path.xls)
excel.Visible = True

So I'm trying to open it like this:

CreateObject("WScript.Shell").Run "excel.exe"
WScript.Sleep 5000

CreateObject("WScript.Shell").Run "excel.exe"
WScript.Sleep 5000

Set excel = GetObject(,"Excel.Application")
Set workbook = excel.Workbooks.Open( excel_file_path, 3) 
excel.Visible = True

This runs just fine when my computer isn't locked. And usually VBScripts can run even when a computer is locked. However, only when the computer is locked, at the line GetObject(,"Excel.Application") I get the following runtime error:

Error:  ActiveX component can't create object: 'GetObject'
Code:   800A01AD
Source: Microsoft VBScript runtime error

I tried changing my Windows 7 settings to never lock after some inactivity, but my company's IT department has enforced that setting.

Is there a way to run this script from a locked screen, or is there an alternative you recommend?

Edit:

It has something to do with the way the excel file is being opened... If I open a new blank excel page than lock the screen, GetObject will work just fine getting that document, but if I open it using the shell then log out, it won't...

Community
  • 1
  • 1
Charles Clayton
  • 17,005
  • 11
  • 87
  • 120

2 Answers2

0

In the section that does not seem to work, add this line after setting visible = TRUE, and see if your calculations then work as expected:

CreateObject("WScript.Shell").AppActivate objExcel.Name

Not sure if it will work, but I am doing similar things in some code and this is what's there. I must have copied or read about it somewhere (wish I could give credit to where).

FYI - I have a vbscript that opens Excel, starts a new workbook, reads a file in as text via a querytable object, then does some automated edits. The code that works for me is:

...
set objExcel = CreateObject("Excel.application") 

' Commented out the following line, so the user can see something is happening.
' objExcel.application.screenupdating   = false  ' Don't update the screen while    shucking and jiving is going on.

objExcel.visible = true
objExcel.application.cursor = 2 ' change cursor to the hourglass.

CreateObject("WScript.Shell").AppActivate objExcel.Name

' Create a new workbook and worksheet
objExcel.workbooks.add
objExcel.worksheets.add

etc
Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • I appreciate you trying to solve the root of my problem, which is a bewildering one. I gave it a go but unfortunately the same thing, the calculations don't actually run, they just flicker and nothing happens. – Charles Clayton Aug 08 '14 at 18:56
  • Hmmm, well this was on my mind as I just finished this program the other day. Good luck! – Gary_W Aug 08 '14 at 19:00
  • Could info from this post help? http://stackoverflow.com/questions/154434/how-do-you-get-excel-to-refresh-data-on-sheet-from-within-vba – Gary_W Aug 08 '14 at 19:10
0

Try the code:

CreateObject("WScript.Shell").Run "excel.exe """ & excel_file_path & """"
WScript.Sleep 5000

CreateObject("WScript.Shell").Run "excel.exe"
WScript.Sleep 5000

Set excel = GetObject(,"Excel.Application")
omegastripes
  • 12,351
  • 4
  • 45
  • 96