Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic Link Update (Excel 2002)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatic Link Update (Excel 2002)

    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

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Link Update (Excel 2002)

    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:

    <font color=blue>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</font color=blue>

    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?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatic Link Update (Excel 2002)

    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

    <pre>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.Refr esh
    .Sheets("Totals").Select
    .Close (True)
    End With

    XLApp.Quit
    Set wkb = Nothing
    Set XLApp = Nothing
    End Sub</pre>


  5. #5
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Link Update (Excel 2002)

    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?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatic Link Update (Excel 2002)

    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

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Link Update (Excel 2002)

    Breakpoints are not retained accross sessions. If you want a "permanent" breakpoint, simply add the "Stop" statement to your code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Link Update (Excel 2002)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •