M
maperalia
I have a program (see below)which is run from excel and that does the
following:
1.- Save as the excel template file (template.xls) using the cells to create
the filename.
2.- Open the word template file (template.doc)
3.- Update the links automatically using the same path and filename in the
step 1
After the program finish I check the link in the word file by clicking “Alt
+ F9†to verify that the new links have taken place. Although the new link
took place I “Select all†under edit menu then I select “Update Link†and I
got the following message:
“Word in unable to create a link to the object you specified, Please insert
the object directly into your file without creating a linkâ€
After I click “OK†I got this message in all my links:
“Error! Not a valid link".
So I discover that even thought the link is there is not reading it.
Could you please tell me how can I fix this problem?
Thanks in advance.
Maperalia
‘&&&&&&&&&&&&&&&&&&&&&&&
Option Explicit
Public Sub SaveExcelOpenWordAndUpdateLinks()
Dim sPath As String
sPath = SaveExcelTemplatelAsSaveAs
OpenWordAndUpdateLinks (sPath)
End Sub
Function SaveExcelTemplatelAsSaveAs() As String
Dim WO As String
Dim grdprp As String
Dim sFilename As String
Dim Progname As String
Dim Filename As String
Dim myDateTime As String
WO = Worksheets("summary BLR").Range("M10")
myDateTime = Format(Worksheets("summary BLR").Range("M9").Value, "yyyymmdd")
Filename = "" & WO & ".grdprp." & myDateTime & ""
Progname = "C:\test\" & Filename & ".xls"
ActiveWorkbook.SaveCopyAs Progname
SaveExcelTemplatelAsSaveAs = Progname
End Function
'***********OPEN THE TEMPLATE WORD FILE ****************************
Sub OpenWordAndUpdateLinks(sPathToExcelFile As String)
Dim wordApp As Object
Dim fNameAndPath As String
Dim Filename As String
fNameAndPath = "C:\test\template.doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True
wordApp.Activate
'wordApp.Run ("C:\test\template.doc!UpdateLinks")
wordApp.Run macroname:="UpdateLinks", vArg1:=sPathToExcelFile
Set wordApp = Nothing
End Sub
'**********************************************************
'NEXT MACRO IS LOCATED IN THE WORD DOCUMENT
Option Explicit
Public Sub UpdateLinks(sPath As String)
Dim alink As Field
Dim linktype As Range
Dim linkfile As Range
Dim linklocation As Range
Dim i As Integer
Dim j As Integer
Dim linkcode As Range
Dim Message, Title, Default, Newfile
Dim counter As Integer
counter = 0
For Each alink In ActiveDocument.Fields
If alink.Type = wdFieldLink Then
Set linkcode = alink.Code
i = InStr(linkcode, Chr(34))
Set linktype = alink.Code
linktype.End = linktype.Start + i
j = InStr(Mid(linkcode, i + 1), Chr(34))
Set linklocation = alink.Code
linklocation.Start = linklocation.Start + i + j - 1
linkcode.Text = linktype & sPath & linklocation
End If
Next alink
End Sub
‘&&&&&&&&&&&&&&&&&&&&&&&
following:
1.- Save as the excel template file (template.xls) using the cells to create
the filename.
2.- Open the word template file (template.doc)
3.- Update the links automatically using the same path and filename in the
step 1
After the program finish I check the link in the word file by clicking “Alt
+ F9†to verify that the new links have taken place. Although the new link
took place I “Select all†under edit menu then I select “Update Link†and I
got the following message:
“Word in unable to create a link to the object you specified, Please insert
the object directly into your file without creating a linkâ€
After I click “OK†I got this message in all my links:
“Error! Not a valid link".
So I discover that even thought the link is there is not reading it.
Could you please tell me how can I fix this problem?
Thanks in advance.
Maperalia
‘&&&&&&&&&&&&&&&&&&&&&&&
Option Explicit
Public Sub SaveExcelOpenWordAndUpdateLinks()
Dim sPath As String
sPath = SaveExcelTemplatelAsSaveAs
OpenWordAndUpdateLinks (sPath)
End Sub
Function SaveExcelTemplatelAsSaveAs() As String
Dim WO As String
Dim grdprp As String
Dim sFilename As String
Dim Progname As String
Dim Filename As String
Dim myDateTime As String
WO = Worksheets("summary BLR").Range("M10")
myDateTime = Format(Worksheets("summary BLR").Range("M9").Value, "yyyymmdd")
Filename = "" & WO & ".grdprp." & myDateTime & ""
Progname = "C:\test\" & Filename & ".xls"
ActiveWorkbook.SaveCopyAs Progname
SaveExcelTemplatelAsSaveAs = Progname
End Function
'***********OPEN THE TEMPLATE WORD FILE ****************************
Sub OpenWordAndUpdateLinks(sPathToExcelFile As String)
Dim wordApp As Object
Dim fNameAndPath As String
Dim Filename As String
fNameAndPath = "C:\test\template.doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True
wordApp.Activate
'wordApp.Run ("C:\test\template.doc!UpdateLinks")
wordApp.Run macroname:="UpdateLinks", vArg1:=sPathToExcelFile
Set wordApp = Nothing
End Sub
'**********************************************************
'NEXT MACRO IS LOCATED IN THE WORD DOCUMENT
Option Explicit
Public Sub UpdateLinks(sPath As String)
Dim alink As Field
Dim linktype As Range
Dim linkfile As Range
Dim linklocation As Range
Dim i As Integer
Dim j As Integer
Dim linkcode As Range
Dim Message, Title, Default, Newfile
Dim counter As Integer
counter = 0
For Each alink In ActiveDocument.Fields
If alink.Type = wdFieldLink Then
Set linkcode = alink.Code
i = InStr(linkcode, Chr(34))
Set linktype = alink.Code
linktype.End = linktype.Start + i
j = InStr(Mid(linkcode, i + 1), Chr(34))
Set linklocation = alink.Code
linklocation.Start = linklocation.Start + i + j - 1
linkcode.Text = linktype & sPath & linklocation
End If
Next alink
End Sub
‘&&&&&&&&&&&&&&&&&&&&&&&