Automatic Link Update (Excel 2002)

Topic

New Reply

I have developed the following: Excel creates a chart based on data automatically grabbed from an Access database (which is actually linked to another db). A Word document has been created with the aforementioned chart embeded and linked. I want to be able to open the Word document and have it display the most recent data. I’m having problems with forcing Excel to automagically update it’s link to Access without having to start Excel. I haven’t been able to figure out the VBA code necessary. Or is there another way? Any help would be appreciated.

Thanks

Reply | Quote

Replies

I don not think it is possible to update the excel file without running excel opening and saving the file.

For example:
Imagine that the excel file is updated and saved on Monday. You change the access data on Tuesday. On Wednesday you open word which links to the Excel file. The excel file will have the data from Monday since that was the last time it was saved.

If you want to automatically be updated, you could have word, open an excel application, open the XL file, update the links and then resave it and closeXL and the file whenever you open the word file.

Or you could have access open an excel application, open the XL file, update the links and then resave it and close XL, before the access file is closed.

Or you could create the chart in Access and have word be linked to the access chart and skip excel and this problem altogether.

Steve

Reply | Quote

Steve–

Thanks for your reply. Since I find the Access chart-making feature rather limiting, I really need to keep Excel in the loop. So…

I am trying to figure out the code necessary for updating the links in Excel and saving the file. I have created a reference in Word to the Excel object, and added the the following code to the Word’s Open procedure:

Private Sub Document_Open()

Dim ES_MCRTOT As Object
Set ES_MCRTOT = GetObject(“es_mcrtot.xls”)
With ES_MCRTOT
.Sheets(“Data_Totals”).Select
.Range(“A2”).Select
.Selection.QueryTable.Refresh
.Sheets(“Totals”).Select
.ActiveWorkbook.Save
End With

End Sub

The idea is to have Word automagically open the Excel file, refresh the data and save the file. When I open the Word file that contains this Open procedure, the embedded charts update their link, but there isn’t any indication that the procedure fires. I have tried to add a breakpoint to the procedure, but it doesn’t seem to save so that when I save the procedure, close Word and reopen the document, my breakpoint is no longer there.

So…Does anybody know what I’m doing wrong–and once I get the procedure to fire, does anyone see anything wrong with my code?

Reply | Quote

Try this. This assumes that file is not open already. Change the path as appropriate. If you want to “watch it” do its thing for debugging, remove the comment from the “xlapp.visible line”

Steve

Option Explicit
Sub UpDateXLFile()
    Dim XLApp As Object
    Dim wkb As Object

    Set XLApp = CreateObject("Excel.application")
    'XLApp.Visible = True  'to make XL visible
    Set wkb = XLApp.workbooks.Open("C:es_mcrtot.xls")


    With wkb
        .Sheets("Data_Totals").Range("A2").QueryTable.Refresh
        .Sheets("Totals").Select
        .Close (True)
    End With

    XLApp.Quit
    Set wkb = Nothing
    Set XLApp = Nothing
End Sub

Reply | Quote

Thanks again Steve. However, I don’t think the Document_Open procedure is firing. I tested this by clearing the procedure and just adding a Msgbox, which did not appear with the opening of the document. Am I right — that this procedure is suppose to fire upon opening the document? Is there something else that I need to do?

Reply | Quote

I don’t work with word too often, but when I put a document_Open macro in a file and opened the file it ran.

I will “speculate” that you have the macro in a module. It should go into the thisdocument object.

Steve

Reply | Quote

Steve–

It seems that I had macro security set to high so the macro would not run. Interesting that I did not get a warning, however. Thanks for your help, your code was useful in helping me to debug mine. Now I get to read up on how to sign a macro. It works like a charm now, except for the security setting.

Reply | Quote

Steve–

It seems that I had macro security set to high so the macro would not run. Interesting that I did not get a warning, however. Thanks for your help, your code was useful in helping me to debug mine. Now I get to read up on how to sign a macro. It works like a charm now, except for the security setting.

Reply | Quote

I don’t work with word too often, but when I put a document_Open macro in a file and opened the file it ran.

I will “speculate” that you have the macro in a module. It should go into the thisdocument object.

Steve

Reply | Quote

Thanks again Steve. However, I don’t think the Document_Open procedure is firing. I tested this by clearing the procedure and just adding a Msgbox, which did not appear with the opening of the document. Am I right — that this procedure is suppose to fire upon opening the document? Is there something else that I need to do?

Reply | Quote

Try this. This assumes that file is not open already. Change the path as appropriate. If you want to “watch it” do its thing for debugging, remove the comment from the “xlapp.visible line”

Steve

Option Explicit
Sub UpDateXLFile()
    Dim XLApp As Object
    Dim wkb As Object

    Set XLApp = CreateObject("Excel.application")
    'XLApp.Visible = True  'to make XL visible
    Set wkb = XLApp.workbooks.Open("C:es_mcrtot.xls")


    With wkb
        .Sheets("Data_Totals").Range("A2").QueryTable.Refresh
        .Sheets("Totals").Select
        .Close (True)
    End With

    XLApp.Quit
    Set wkb = Nothing
    Set XLApp = Nothing
End Sub

Reply | Quote

Breakpoints are not retained accross sessions. If you want a “permanent” breakpoint, simply add the “Stop” statement to your code.

Reply | Quote

Breakpoints are not retained accross sessions. If you want a “permanent” breakpoint, simply add the “Stop” statement to your code.

Reply | Quote

Steve–

Thanks for your reply. Since I find the Access chart-making feature rather limiting, I really need to keep Excel in the loop. So…

I am trying to figure out the code necessary for updating the links in Excel and saving the file. I have created a reference in Word to the Excel object, and added the the following code to the Word’s Open procedure:

Private Sub Document_Open()

Dim ES_MCRTOT As Object
Set ES_MCRTOT = GetObject(“es_mcrtot.xls”)
With ES_MCRTOT
.Sheets(“Data_Totals”).Select
.Range(“A2”).Select
.Selection.QueryTable.Refresh
.Sheets(“Totals”).Select
.ActiveWorkbook.Save
End With

End Sub

The idea is to have Word automagically open the Excel file, refresh the data and save the file. When I open the Word file that contains this Open procedure, the embedded charts update their link, but there isn’t any indication that the procedure fires. I have tried to add a breakpoint to the procedure, but it doesn’t seem to save so that when I save the procedure, close Word and reopen the document, my breakpoint is no longer there.

So…Does anybody know what I’m doing wrong–and once I get the procedure to fire, does anyone see anything wrong with my code?

Reply | Quote

I don not think it is possible to update the excel file without running excel opening and saving the file.

For example:
Imagine that the excel file is updated and saved on Monday. You change the access data on Tuesday. On Wednesday you open word which links to the Excel file. The excel file will have the data from Monday since that was the last time it was saved.

If you want to automatically be updated, you could have word, open an excel application, open the XL file, update the links and then resave it and closeXL and the file whenever you open the word file.

Or you could have access open an excel application, open the XL file, update the links and then resave it and close XL, before the access file is closed.

Or you could create the chart in Access and have word be linked to the access chart and skip excel and this problem altogether.

Steve

Reply | Quote