+ Reply to Thread
Results 1 to 6 of 6

open excel source document in background when i open word document

  1. #1
    Registered User
    Join Date
    02-28-2013
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    open excel source document in background when i open word document

    I am working with a word document that has links to an excel workbook. I have tried this with task scheduler but it doesn't quite do what I need so I will play in this pond for a bit.

    Basically, I need a vba that will open the excel file when the word file is opened. The excel file already has a code that will refresh the data and then automatically close the file after a minute. The goal is to open the excel file refresh that data, then refresh the data in the word document so it will be current and my boss will not have to mess with either file.

    I have spent some time on this and any help would be appreciated.

    -Adrian

  2. #2
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,642

    Re: open excel source document in background when i open word document

    Hi Adrian,

    The code to open an Excel file is fairly convoluted. For some links to such code, see: http://www.excelforum.com/word-progr...ple-words.html. Given what you describe, the code to update the Excel file should also be embedded in or called by the macro that opens it.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    02-28-2013
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Re: open excel source document in background when i open word document

    Thanks I appreciate it. This is what I have come up with...

    Sub AutoOpen()
    Word.Options.UpdateLinksAtOpen = True
    Sub Auto_run_OpenExcelFile()
    Dim xlApp As Object
    Dim MyFile As String
    Rem set-up
    MyFile = "G:\Daily Operations Report\Executive\automated reports.xlsm"
    ' -- find out if excel is already open
    If Tasks.Exists(Name:="Microsoft Excel") = False Then
    ' -- if not open then open it
    Set xlApp = CreateObject("Excel.Application")
    ElseIf Tasks.Exists(Name:="Microsoft Excel") = True Then
    ' -- Excel is is already open, just return the object
    Set xlApp = GetObject(, "Excel.Application")
    Else
    ' -- Excel could not be found
    MsgBox "The Application Excel could not be opened."
    ' -- stop the routine
    End
    End If
    ' -- make sure its visible
    xlApp.Application.Visible = True
    ' -- and open the workbook
    On Error GoTo ErrHandler
    xlApp.Workbooks.Open MyFile
    Set xlApp = Nothing
    Exit Sub
    ErrHandler:
    Select Case Err.Number
    Case "1004"
    MsgBox "Error number: " & Str(Err.Number) & vbCr & _
    "The Workbook """ & MyFile & """ could not be found.", _
    vbCritical, "Workbook could not be opened"
    Case Else
    MsgBox Str(Err.Number) & " " & Err.Description, vbCritical, "VBA Error"
    End Select
    Set xlApp = Nothing
    End Sub

  4. #4
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,642

    Re: open excel source document in background when i open word document

    Hi Adrian,

    I see that you get the workbook open (xlApp.Workbooks.Open MyFile), but there's no code to do anything with it after that (not even to close it or the Excel session).

    PS: When posting code, please use the code tags. They're on the toolbar.

  5. #5
    Registered User
    Join Date
    02-28-2013
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Re: open excel source document in background when i open word document

    Thanks Paul. i guess newbies always stand out... The excel document automatically refreshes on open. It also includes a timed close after two minutes.

    The issue that I am having now is that I can't get this to auto run when the word document opens because of the update links question...

    Do you have any thoughts on how to work around that?

    -Adrian

  6. #6
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,642

    Re: open excel source document in background when i open word document

    In this case, I'd be inclined to forego the automatic links altogether - simply have your Word code trigger the Excel workbook update, then pull in the updated values to wherever you want them. Since you're already opening the workbook, there'd be little overhead. Plus, once you've updated the workbook and the document, you can close the workbook immediately, releasing it for other use. The wholw process probably wouldn't take more than a few seconds.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1