Opening Excel from Word

[jarnold231]

Good evening. I'm hoping someone here can give me some help with what I assume is a relatively simple VBA programming challenge, but which is well beyond my VBA capabilities.



I am trying to create a macro that performs the following actions.

1. User opens .docm Word file
2. VBA automatically opens a referenced
3. Excel File The Excel file runs a Private_sub (this is all working)
4. User answers a Application.Input box in the Excel Word document updates all links from Excel File
5. Word requires the user to SaveAs the Word document

I understand that the VBA needs to be embedded in the Microsoft Word Objects "ThisDocument" section of VBA. I've tried a number of scripts that I've found online which include the use of Objects (things like Dim excelApp As Excel.Application), but I always get a runtime error.

Really hoping someone can give me a bit of an idiots guide to getting the basic structure of this set up.

Any help greatly appreciated.

Jake

[macropod]

Code like:
Dim excelApp As Excel.Application
indicates the use of early binding. Did you set a reference to Excel in the VBE, via Tools|References?

[jarnold231]

I've added MS Excel Objects and it now works and opens the file, but only if specifically run rather than automatically. My code is currently as follows:

    

PrivateSub runOnOpen

    Call openExcel
    Call UpdateAllFields


End Sub

Sub openExcel()

  Dim excelApp As Excel.Application
  Dim openExcel As Workbook
  Dim var1 As Integer

  Set excelApp = New Excel.Application
  Set openExcel = excelApp.Workbooks.Open("filename")
  excelApp.Visible = True

End Sub

Sub UpdateAllFields()

    Application.DisplayAlerts = False
    ActiveDocument.Fields.Update
    Application.DisplayAlerts = True

End Sub

[macropod]

Cross-posted at: https://stackoverflow.com/questions/...file-from-word
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[macropod]

Originally Posted by jarnold231

I've added MS Excel Objects and it now works and opens the file, but only if specifically run rather than automatically.

You should look at the topic of auto macros for Word; Your 'runOnOpen' macro is named incorrectly for that.