Automating Excel (XL2002)
 

I have created a workbook that includes data on one sheet
and a chart based on the data on another. The data is
linked from an Access .mdb. The chart is, in turn,
linked to a Word document. This document contains
several of these linked charts, in a chain from Access to
Excel to Word.

I used Word to automate Excel to requery the database and
therefore update the data on the worksheet, which in turn
updates the chart as it presents in Word. The problem is
that when this procedure is first run (I placed it in the
Document_Open() procedure), it somehow locks the Excel
file so that I have no subsequent access to the file
directly. Interestingly, the procedure still works fine
and continues to update the worksheet. I have tried
restarting Excel and even rebooting, but the worksheet
remains inaccessible. I can't even delete it. Anyone
have any ideas?

Here is the code from Word:

Private Sub Document_Open()

Dim wbk As Object
Set wbk = GetObject("c:\filename.xls")
With wbk
.Sheets("Data_Totals").Range("A2").QueryTable.Refr esh
.Sheets("Totals").Select
.Close (True)
End With
Set wbk = Nothing
End Sub

Automating Excel (XL2002)
 

Randy

Try adding False for the Background argument to Refresh, as in:

.Sheets("Data_Totals").Range("A2").QueryTable.Refr esh False

and see if that helps.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Randy" wrote in message
...
I have created a workbook that includes data on one sheet
and a chart based on the data on another. The data is
linked from an Access .mdb. The chart is, in turn,
linked to a Word document. This document contains
several of these linked charts, in a chain from Access to
Excel to Word.

I used Word to automate Excel to requery the database and
therefore update the data on the worksheet, which in turn
updates the chart as it presents in Word. The problem is
that when this procedure is first run (I placed it in the
Document_Open() procedure), it somehow locks the Excel
file so that I have no subsequent access to the file
directly. Interestingly, the procedure still works fine
and continues to update the worksheet. I have tried
restarting Excel and even rebooting, but the worksheet
remains inaccessible. I can't even delete it. Anyone
have any ideas?

Here is the code from Word:

Private Sub Document_Open()

Dim wbk As Object
Set wbk = GetObject("c:\filename.xls")
With wbk
.Sheets("Data_Totals").Range("A2").QueryTable.Refr esh
.Sheets("Totals").Select
.Close (True)
End With
Set wbk = Nothing
End Sub