Embedded Excel in Word VBA

[James Bowyer]

OK, I realise this isn't directly an Access question (although the spreadsheet I want to link to is created by access), but here goes:

I have created a pecie of software in Access that creates a standardised set of folders for projects, which contain a mixture of excel and word documents. It also creates a spreadsheet in one of the files which contains all the project information (locked, so you can only update it by using access to create a new one, and archives the old one with a revision number).

I have managed to create an Excel Macro which automatically updates all the links in an excel file to look at the correct details, which is as follows:

 
Sub Update()
On Error GoTo Update_error
Dim errorstate As Integer
errorstate = 1
 
Dim s As String
Dim splitdone As Variant
 
s = ActiveWorkbook.Path
 
splitdone = Split(s, "\")
 
Dim newpath As String
Dim newpath2 As String
 
newpath = "\\" & splitdone(2) & "\" & splitdone(3) & "\" & splitdone(4) & "\" & splitdone(5) & "\" & splitdone(6) & "\a. Site Details\SiteDetails.xlsx"
'MsgBox newpath, vbInformation, "path"
 
 
Dim currentlinks As Variant
currentlinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(currentlinks) Then
Dim i As Integer
 
For i = 1 To UBound(currentlinks)
 
ActiveWorkbook.ChangeLink Name:= _
        currentlinks(i), NewName:= _
        newpath, Type:=xlExcelLinks
Next i
 
Else
MsgBox "No Links Found", vbCritical, "SCM"
End If
Exit Sub
 
 
Line1:
errorstate = errorstate + 1
Dim currentlinks1 As Variant
currentlinks1 = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(currentlinks1) Then
newpath2 = splitdone(0) & "\" & splitdone(1) & "\" & splitdone(2) & "\" & splitdone(3) & "\a. Site Details\SiteDetails.xlsx"
'MsgBox newpath2, vbInformation, "path 2"
Dim i1 As Integer
 
For i1 = 1 To UBound(currentlinks1)
 
ActiveWorkbook.ChangeLink Name:= _
        currentlinks1(i1), NewName:= _
        newpath2, Type:=xlExcelLinks
Next i1
 
Else
MsgBox "No Links Found", vbCritical, "SCM"
End If
 
 
Exit Sub
 
Update_error:
Select Case errorstate
Case Is = 1
GoTo Line1
Case Else
MsgBox "Unable To Update Links", vbCritical, "SCM"
End Select
 
End Sub
 

Which works perfectly. However, obviously it only works in excel, not word. I have tried embedding Excel worksheets in word and using the same links as I would use in the excel spreadsheets, which appears to work fine, however, I can't get them to update using the above code. It appears that the problem is the ActiveWorkbook.Changelinks part, it doesn't pick up the excel workbook embedded in the document and therefore won't change it.

Any idea if:
a) there is a way to make the code see the embedded worksheets

Or

b) there is a better way of having text that updates itself in Word.

I have a feeling B is the better option... ;)

Thanks!

[NeoPa]

Check out Application Automation. I suspect this will help you resolve all your difficulties (but let us know if you need further help). Pay particular attention to post #2 with regards to developing code for a foreign environment (Word from Excel or Excel from Access etc).