5

I'm having a problem with a script not updating an excel file, and I reduced it to the following problem:

If I open an excel file, I can go to the Formulas tab and click "Calculate Now" and it'll spend a bit of time updating all the calculations.

If I run a VBScript just to open the file (see following code), if I go to the Formulas tab and click "Calculate Now" it'll just refresh immediately and nothing will change. 

Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True
Set objXLWb = objXLApp.Workbooks.Open(file_path.xls)

I've tried all sorts of stuff like:

objXLApp.Calculation = xlAutomatic
objXLApp.Calculate
objXLApp.CalculateFull
objXLApp.CalculateFullRebuild
objXLWb.RefreshAll
objXLWs.EnableCalculation = True
objXLWs.Calculate

But those seem to do the same thing as going to the tab and clicking "Calculate" resulting in just a quick refresh and the excel page not trying to update at all.

The same is true when using python's win32com module. I can't run calculations in the opened file.

import win32com.client as win32

excel = win32.Dispatch('Excel.Application')
excel.Visible = True
excel_workbook = excel.Workbooks.Open(file_path.xls)

The same is also true using PowerShell. 

$excel = New-Object -com excel.application
$excel.Visible = $True
$workbook = $excel.Workbooks.Open( $file_path )

So why does opening a file with these languages somehow shut off the ability to calculate the formulas?

Community
  • 1
  • 1
Charles Clayton
  • 17,005
  • 11
  • 87
  • 120
  • 1
    Interesting. Have you added any code that's automatically executed when your workbook is opened (in `Workbook_Open`, for example)? – djikay Jul 29 '14 at 19:05
  • I'm not sure what you mean, but no, I haven't added any code other than what I've posted to anything. – Charles Clayton Jul 29 '14 at 19:57
  • Do your formulas contain external references? – Bond Jul 29 '14 at 19:59
  • Try `objXLApp.CalculateBeforeSave = True` and then `objXLWb.Save`. – Bond Jul 29 '14 at 20:05
  • Yes, actually. Some formulas get data from PI Software like so: http://www.osisoft.com/software-support/products/PI_DataLink.aspx. However, when the script opens up the folder, I still have the PI add-on tabs on the page. – Charles Clayton Jul 29 '14 at 20:05
  • The 2nd paramater to `Workbooks.Open()` determines whether external links are updated. If you pass a value of `3`, Excel will update all links (both remote and external). – Bond Jul 29 '14 at 20:09
  • Afraid `CalculateBeforeSave` doesn't work. It's like just opening it with one of the scripts removes it's ability to calculate and it just quickly refreshes. – Charles Clayton Jul 29 '14 at 20:10
  • Damn, that second parameter seemed promising but unfortunately it's the same deal. Thanks, these are good suggestions though. – Charles Clayton Jul 29 '14 at 20:14
  • Hmm, I would think if it's an issue just from _opening_ the file via VBScript, it would have something to do with the `Open()` function. Something else you can try (just for fun)... launch Excel using `Shell.Run` and then use `GetObject` to retrieve the Excel instance. Then try your recalc functions. – Bond Jul 29 '14 at 20:27
  • When I enter file path in cmd, it opens up and starts to automatically calculate, but when I enter `CreateObject("WScript.Shell").run """filepath""",1` it just opens up excel, but not the file. – Charles Clayton Jul 29 '14 at 20:46
  • I'll post an answer so I can describe something you can try... – Bond Jul 29 '14 at 20:54

3 Answers3

2

I found the problem, opening excel pages with VBScript (or Powershell) doesn't automatically include the Addins that are included when opening manually, so I needed to manually add these two addins.

excel.AddIns.Add("C:\Program Files (x86)\PIPC\Excel\PITrendXL.xla").Installed = True
excel.AddIns.Add("C:\Program Files (x86)\PIPC\Excel\pipc32.xll").Installed = True
Charles Clayton
  • 17,005
  • 11
  • 87
  • 120
1

Try this. Just for testing.

' Launch Excel...
CreateObject("WScript.Shell").Run "excel.exe"

' Wait for it to load...
WScript.Sleep 5000

' Get the running instance...
Set Excel = GetObject(, "Excel.Application")

' Open your workbook...
Excel.Workbooks.Open strPath

' Now go and click the Calculate button and see if it works.
Bond
  • 16,071
  • 6
  • 30
  • 53
  • At `Set Excel = GetObject(, "Excel.Application")` I get an `ActiveX component can't create object` error. And when I include the filename before the comma I get `File name or class name not found during Automation operation: 'GetObject'`. – Charles Clayton Jul 29 '14 at 21:11
  • 1
    Is Excel loaded and running before the message appears? – Bond Jul 30 '14 at 00:52
  • Very interesting! If excel isn't open and I run the script, I get the ActiveX error. If I open up excel then run the script, the program will run just fine, and yes, it will allow formulas to populate! – Charles Clayton Jul 30 '14 at 15:49
  • Yeah. So for some reason running only one `shell.run excel` doesn't work, but if I open up two different blank workbooks, then I can get the workbook and run `excel.Calculate` and it'll work. The problem seems to only be with `Set objXLApp = CreateObject("Excel.Application")`, whereas `Set objXLApp = GetObject(,"Excel.Application") ` works. – Charles Clayton Jul 30 '14 at 16:12
  • Yeah, it definitely had to be something with either `CreateObject` or `Workbooks.Open`. By using `Shell.Run` and `GetObject` to launch and connect to Excel instead, it should tell us whether `CreateObject` was the culprit. – Bond Jul 30 '14 at 20:47
0

Try adding a module with this in it

    Sub Auto_Open()
        ActiveWorkbook.RefreshAll
        Calculate
    End Sub
Pike7893
  • 80
  • 1
  • 4
  • 13
  • I included that in the code but nothing changed I'm afraid. Was there a specific place you meant to put it? – Charles Clayton Jul 29 '14 at 20:15
  • Just in the workbook inside a new module... How many excel files is this for? – Pike7893 Jul 29 '14 at 20:20
  • I had not seen the comments above, if you have external formulas inside the workbook you are opening those need an update command different from calculate. Such as ActiveWorkbook.RefreshAll... see above edit – Pike7893 Jul 29 '14 at 20:24
  • Sorry, that doesn't seem to be doing anything either. – Charles Clayton Jul 29 '14 at 20:53
  • Does it do change automatically when you open it manually with that? How many files is this for? And is the script only opening the files? – Pike7893 Jul 29 '14 at 20:55
  • Yes it does, it's only for one file, the script essentially needs to open up an excel spreadsheet with all these formulas (which takes about a minute to completely populate), plug in some various information from different sources, then save a pdf of the sheet. At the moment, all the information gets put in, but because I can't calculate the form in the script, all those values are either outdated or `#NAME?` errors. – Charles Clayton Jul 29 '14 at 21:03
  • That is strange that it will update correctly when you manually open it... You can possibly do without the script and do some IO in VBA in the spreadsheet.... Then maybe open it with a task scheduler... I know that works – Pike7893 Jul 29 '14 at 21:08