[AccessD] Updating links in Excel from Access form

Hi Oleg

> Guys,

Well, as a warning, that excludes a handful or so very distinct and
competent listers ... 

> I need to be able to update links in excel workbook from  Access form.
> Does anyone how it is possible ?

Yes, run a function something like the one below which is based on a
similar function from Mr. Colby.

First, run it with a list of your macros:

  booSuccess = RunExcelMacros( _
    "L:\PCO\YieldInterpolation.xls", _
    "GetRates", "GetRates2")
    
Next, run it to terminate Excel:

  booSuccess = RunExcelMacros( _
    vbNullString, _
    vbNullString)

You can, of course, modify the function to run Excel and then
terminate in one go.


<code>

Function RunExcelMacros( _
  ByVal strFileName As String, _
  ParamArray avarMacros()) As Boolean

  Debug.Print "xl ini", Time
  
  On Error GoTo Err_RunExcelMacros

  Static xlApp      As Excel.Application
  Dim xlWkb         As Excel.Workbook
  
  Dim varMacro      As Variant
  Dim booSuccess    As Boolean
  Dim booTerminate  As Boolean

  If Len(strFileName) = 0 Then
    ' Excel is to be terminated.
    booTerminate = True
  End If

  If xlApp Is Nothing Then
    If booTerminate = False Then
      Set xlApp = New Excel.Application
    End If
  ElseIf booTerminate = True Then
    xlApp.Quit
    Set xlApp = Nothing
  End If
  
  If booTerminate = False Then
    Set xlWkb = xlApp.Workbooks.Open(FileName:=strFileName, UpdateLinks:=0, ReadOnly:=True)
  
    ' Make Excel visible (for troubleshooting only) or not.
    xlApp.Visible = False 'True
    
    For Each varMacro In avarMacros()
      If Not Len(varMacro) = 0 Then
        Debug.Print "xl run", Time, varMacro
        booSuccess = xlApp.Run(varMacro)
      End If
    Next varMacro
  Else
    booSuccess = True
  End If
  
  RunExcelMacros = booSuccess

Exit_RunExcelMacros:

  On Error Resume Next
    
  If booTerminate = False Then
    xlWkb.Close SaveChanges:=False
    Set xlWkb = Nothing
  End If
  
  Debug.Print "xl end", Time
  Exit Function

Err_RunExcelMacros:
  Select Case Err
    Case 0      ' Insert here errors you wish to ignore.
      Resume Next
    Case Else   ' All other errors will trap.
      DoCmd.Beep
      MsgBox "Error: " & Err & ". " & Err.Description, vbCritical + vbOKOnly, "Running macro " & varMacro
      Resume Exit_RunExcelMacros
  End Select

End Function

</code>

Beware of line breaks.

/gustav


> Private Sub cmdUPY_Click()

>     DoCmd.RunMacro "GetRates"

> ‘refresh L:\PCO\YieldInterpolation.xls

>     DoCmd.RunMacro "GetRates2"

> End Sub