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 :
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
Thank you for your help !