Word VBA : fields update



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:OBLIGSBreveModelesChamps automatiques.xls", ReadOnly:=True)

xlApp.Visible = True

End If


End sub

Thank you for your help !
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!