Word VBA : field update

[shimuno]

Hello

I've been working on a macro enabling a user to update a Word document linked to an Excel workbook. The macro open the Excel file if it closed, then the user can modify the Excel file before updating the Word document.

The code below works quite well approximately 9 times out of 10. Unfortunately, sometimes the following errors happen :
- the Excel file does not open
- the Excel file opens but it is impossible to update the fields
- the Excel file window is not activated, so the user thinks that it cannot open it (actually it is opened, but hidden behind the Word window).

Any ideas ?

Here is the code :


Sub Updatelinks


Dim xlApp As Object
Dim xlWB As Object

Set xlApp = CreateObject("Excel.Application")

On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")


Set xlWB = xlApp.Workbooks("Champs automatiques.xls")

On Error GoTo 0


'Open the Excel file if it is closed

If xlWB Is Nothing Then

xlApp.AskToUpdateLinks = False

Set xlWB = xlApp.Workbooks.Open("H:\OBLIGS\Breve\Modeles\Champs automatiques.xls", ReadOnly:=True)

xlApp.Visible = True

End If

ActiveDocument.Fields.Update

End sub


Thank you for your help !

 

[SkipVought]

I'd suggest you open VBA Help and get help on GetObject. You've got a few more things to include, as you will see.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I would also use the explicit server name, rather than a drive letter...

Set xlWB = xlApp.Workbooks.Open("[b]\\YourServerName[/b]\OBLIGS\Breve\Modeles\Champs automatiques.xls", ReadOnly:=True)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}