Hi,

 I am trying to open a workbook from a VB program and get
 the work book to calculate (calculation takes 7 mins so the user wants the workbook to be updated by a program and for the user not to have to endure this wait:

      oXlApp.SendKeys "%^{F9}"

  This line of code works when the workbook is opened by human but not when opened by the program. Cells containing formulas end up
 #NAME? in this case. The spreadsheet was written by another user - so the formulas are not in my domain.

Any help much appreciated. Have searched thru my copy of Excel 2002 VBA - but was not able to find anything to solve my problem.

thanks
jeannief

you should allow the automation to force a calculate on the workbook.
oXlApp.Calculate
this should force a recalculation of the whole excel application.

alternatively...
oxlapp.Worksheets(1).Calculate
should calculate a specific worksheet in a specific workbook

cheers

Matt

Hi Matt,

Thanks for the response. However, i had already tried these approaches without success.

Set oXlApp = CreateObject("Excel.Application")
    oXlApp.Visible = False

    Set wb = oXlApp.Workbooks.Open(sFileName)
    oXlApp.Calculation = xlCalculationManual

    'oXlApp.ScreenUpdating = True
    'oXlApp.Visible = True

    sNow = Format(Now(), "dd-mmm-yy hh:mm:ss")
    oXlApp.Worksheets("Menu").Range("rSelectedTime").V alue = sNow

    oXlApp.SendKeys "%^{F9}" '1
    oXlApp.Calculate '2
    For Each Sheet In oXlApp.Worksheets '3
        Sheet.Calculate
    Next

Additionally, I had put the code in the workbook itself and used the VB program to set a "flag" in a cell, so that the workbook_open method would check the flag and then execute the recalculation. Once again, when the workbook is opened manually the method executes and the workbook updates, but when opened by the vb program the function executes but the calculation results in formula cells with #NAME?

thanks again.

j

For the benefit of others who may have the same problem:

The issue was that the functions used in the workbook were in an addin xll. It seems that these do not load when a workbook is opened using automation. It is necessary to open a blank workbook, remove and re-add the addin. (the addin is checked but not loaded by default)

    oXlApp.Workbooks.Add
    oXlApp.Visible = False
    Set oAddin = oXlApp.AddIns.Add("your addin .xll path ", True)
    oAddin.Installed = False
    DoEvents
    oXlApp.AddIns.Add "your addin .xll path" , True
    oAddin.Installed = True

    Set wb = oXlApp.Workbooks.Open(sFileName)